Find & Replace / Loop & Vlookup
If by new data you mean Columns A and B, that is not correct. You stated it
was on sheet1, so to change to Datasheet you would replace Sheet1 with
datasheet.
Sub DoReplacements()
Dim rng as Range, rng1 as Range
With Worksheets("DataSheet")
set rng = .Range(.cells(1,1),.Cells(rows.count,1).End(xlup))
End With
set rng1 = Range(Cells(1,"G"), Cells(rows.count,"G").End(xlup))
for each cell in rng
rng1.Replace cell.Value, cell.offset(0,1).Value
Next
End Sub
or to specifically refer to specific sheets
Sub DoReplacements()
Dim rng as Range, rng1 as Range
With Worksheets("DataSheet")
set rng = .Range(.cells(1,1),.Cells(rows.count,1).End(xlup))
End With
With Worksheets("Sheet2")
set rng1 = .Range(.Cells(1,"G"), .Cells(rows.count,"G").End(xlup))
End With
for each cell in rng
rng1.Replace cell.Value, cell.offset(0,1).Value
Next
End Sub
Then it shouldn't make any difference what sheet is active. Adjust sheet
names to fit your situation.
--
Regards,
Tom Ogilvy
"thom hoyle" wrote in message
...
That worked great Tom.. thanks...
To have this work, I must have the "new data" on the current worksheet.
How
would I run this if the Column replacments data were on say "DATASHEET"
....
thanks
"Tom Ogilvy" wrote:
typo on rows,count should be rows.count
set rng1 = Range(Cells(1,"G"), Cells(rows.count,"G").End(xlup))
--
Regards,
Tom Ogilvy
"thom hoyle" wrote in message
...
Ok, I tried that but I keep getting a failue:
Error message:
Compile error: Wrong number of arguments or invalid properties
assignment:
On the 2nd Cells with this part of the code:
set rng1 = Range(Cells(1,"G"), Cells(rows,count,"G").End(xlup))
thanks
thom
"Tom Ogilvy" wrote:
Make the sheet with column G the active sheet then run a macro like:
Sub DoReplacements()
Dim rng as Range, rng1 as Range
With Worksheets("Sheet1")
set rng = .Range(.cells(1,1),.Cells(rows.count,1).End(xlup))
End With
set rng1 = Range(Cells(1,"G"), Cells(rows,count,"G").End(xlup))
for each cell in rng
rng1.Replace cell.Value, cell.offset(0,1).Value
Next
End Sub
Test this on a copy of your workbook
--
Regards,
Tom Ogilvy
"thom hoyle" wrote in message
...
I'm working on this data dump. Data is only in Column G
Column G contains
249
271
333
etc..
I need to find every instance of "249" and replace with "249 DNA"
..
I would like it to look at my Sheet1 For the Find & Replace
starting
in
Column A, Row 1, and replace with Column B data.
Make Sense...
thanks...
|