Find & Replace / Loop & Vlookup
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... |
Find & Replace / Loop & Vlookup
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... |
Find & Replace / Loop & Vlookup
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... |
Find & Replace / Loop & Vlookup
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... |
Find & Replace / Loop & Vlookup
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... |
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... |
All times are GMT +1. The time now is 09:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com