UtterAccess Discussion Forums > Macro: Find And Replace

UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel

jesmiki

Feb 10 2012, 05:02 PM

Greetings,

I am looking for a macro that will find and replace 14k records.

For example in sheet 1 I have 2 columns:

1. column "a" has the find value which can possibly be any where in sheet 2.

2. column "b" has the replace value

Can someone help me find a solution?

Best,

Carlo.

MedicalServices

Feb 10 2012, 05:44 PM

The following will work assuming you are looking for 100 search values in column A sheet 1

CODE

Public Sub Find_Replace_Value()
Dim SheetToSearch As Worksheet
Dim Sheet1Value As Range
Set SheetToSearch = ActiveWorkbook.Worksheets(2)
Set Sheet1Value = ActiveWorkbook.Worksheets(1).Range("a1:a100")
For Each x In Sheet1Value.Cells
SheetToSearch.Columns.Replace x, x.Offset(0, 1), xlByColumns
Next
End Sub

you can change the size of the Sheet1Value to suit your needs
Hope this helps!

jesmiki

Feb 10 2012, 05:50 PM

Worked like a charm!!!!!

jesmiki

Feb 10 2012, 05:50 PM

THANK YOU!

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.