• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find and repalce value in different column

danielle2706

New Member
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

Excel Ninja
Staff member
Danielle2706

You are so very close, just missing 1 line

[pre]
Code:
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]
 
Top