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...

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...

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...

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...

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...

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...