How to offsett .Replace

[Tim Hanson]

The code replaces the value in Col C of the sheet "Age" if it matches a value in col A on the Sheet "FindReplace" with it paired value in col B.

This works good but I need to continue to match the value in col C on the "Age" sheet but actuly replace the value in col B when a value is matched

I have tryed offset(,-1) but this has no effect

I have uploaded a workbook that show more clearly what I need

Thanks

.Columns("C:C").Offset(,-1).Replace FindValues(i, 1), ReplaceValues(i, 1), xlWhole, xlByColumns, False

Sub FindReplace()
Dim FindValues As Variant, ReplaceValues As Variant
Dim wsFR As Worksheet, wsTarget As Worksheet
Dim lRow As Long, i As Long

      Set wsFR = ThisWorkbook.Worksheets("FindReplace")
Set wsTarget = ThisWorkbook.Worksheets("Age")
             lRow = wsFR.Range("A" & wsFR.Rows.Count).End(xlUp).Row
   
    With wsTarget
              FindValues = wsFR.Range("A2:A" & lRow).Value
        ReplaceValues = wsFR.Range("B2:B" & lRow).Value

        For i = LBound(FindValues) To UBound(FindValues)
            .Columns("C:C").Replace FindValues(i, 1), ReplaceValues(i, 1), xlWhole, xlByColumns, False
        Next i
    End With
End Sub

 

[Debraj]

This works good but I need to continue to match the value in col C on the "Age" sheet but actuly replace the value in col B when a value is matched

Find and Replace is works for.. Find something and then REPLACE the same thing , in the same area.. In case of Find something, and INSERT any other thing in, in some other area.. you can use.. Logic like VLOOKUP, SELECT case logic..

Let us know, if you need help to create the logic..

 

[Tim Hanson]

Thanks Debraj, yes, I would love some help with this if you have the time, but will try on my own as well

 

[Tim Hanson]

I guess the fastest way to do this is just copy the Col C to Col B and run my code

 

[Debraj]

WOW.. thats also a good approach.. anyway.. try this too..

Sub Chandoo20472()
  With Sheets("Age")
  lr = .Range("A" & .Rows.Count).End(xlUp).Row
  With .Range("B2:B" & lr)
  .Value = _
  "=VLOOKUP(C2," & Sheets("FindReplace").Range("A1").CurrentRegion.Address(1, 1, , 1) & ",2,0)"
  .Value = .Value
  End With
  End With
End Sub

 

[Tim Hanson]

Debraj that worked great!

I will have to study your approach I am not familiar with "CurrentRegion.Address()"

Thank you for the help!