Find and repalce value in different column

[danielle2706]

Hi Team,


My code finds text in column G and uses a refence sheet to replace the correct text in the same column. Now I need to use the same code to find reference in Range(b:b) and offset the value in column O. My only problem is that its not exactly. Instead of changing the value in sheet 1 it changed in the refence sheet column A.


I know I need to Offset(0, 14).Value but Im not sure where that will go or because I have added additional funtions if I need to declare string.


here is my code..


Sub SupplierID()

'

For i = 2 To 20

gFind = ActiveWorkbook.Worksheets("Reference").Cells(i, 1).Value


gNewValue = ActiveWorkbook.Worksheets("Reference").Cells(i, 2).Value


Columns("G:G").Select

Range("G1").Activate


Cells.Replace What:=gFind, Replacement:=gNewValue, LookAt:=xlPart, SearchOrder _

:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:= _

False


Next


End Sub

 

[Hui]

Danielle2706

You are so very close, just missing 1 line

[pre]

Sub SupplierID()

Sheets("Sheet1").Select 'This one
For i = 2 To 20
gFind = ActiveWorkbook.Worksheets("Reference").Cells(i, 1).Value
gNewValue = ActiveWorkbook.Worksheets("Reference").Cells(i, 2).Value

Columns("G:G").Select
Range("G1").Activate

Cells.Replace What:=gFind, Replacement:=gNewValue, LookAt:=xlPart, SearchOrder:=xlByColumns, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next

End Sub

[/pre]