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" <thomhoyle@discussions.microsoft.com> wrote in message
news:DA8AB636-F63D-4EBD-9D06-C97F54F5F1D3@microsoft.com...
> 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...

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" <thomhoyle@discussions.microsoft.com> wrote in message
> news:DA8AB636-F63D-4EBD-9D06-C97F54F5F1D3@microsoft.com...
> > 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...
>
>
>

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" <thomhoyle@discussions.microsoft.com> wrote in message
news:8EA0B493-0D1F-45C4-924B-7F25E22A2C5E@microsoft.com...
> 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" <thomhoyle@discussions.microsoft.com> wrote in message
> > news:DA8AB636-F63D-4EBD-9D06-C97F54F5F1D3@microsoft.com...
> > > 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...
> >
> >
> >

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" <thomhoyle@discussions.microsoft.com> wrote in message
> news:8EA0B493-0D1F-45C4-924B-7F25E22A2C5E@microsoft.com...
> > 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" <thomhoyle@discussions.microsoft.com> wrote in message
> > > news:DA8AB636-F63D-4EBD-9D06-C97F54F5F1D3@microsoft.com...
> > > > 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...
> > >
> > >
> > >
>
>
>

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" <thomhoyle@discussions.microsoft.com> wrote in message
news:011E8C21-79BC-41B0-9693-B672EF6334F1@microsoft.com...
> 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" <thomhoyle@discussions.microsoft.com> wrote in message
> > news:8EA0B493-0D1F-45C4-924B-7F25E22A2C5E@microsoft.com...
> > > 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" <thomhoyle@discussions.microsoft.com> wrote in message
> > > > news:DA8AB636-F63D-4EBD-9D06-C97F54F5F1D3@microsoft.com...
> > > > > 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...
> > > >
> > > >
> > > >
> >
> >
> >