find and replace - based on 3 character in adjacent cell

Hi

Put the following values in the range I7:J9
101,Apple
102,Orange
409,Grape

Give that range a defined name of LURng.

Private Sub btnReplace_Click()
    Worksheets("Sheet1").Range("B3").Activate
    
    Dim MyCell As Range
    
    For Each MyCell In Range("C3:C10")
        
        If MyCell <> "" Then
          If Left(MyCell, 1) = "A" Then
            MyCell.Offset(0, -1).Value = WorksheetFunction.VLookup(Val(Mid(MyCell, 3, 3)), Range("LURNG"), 2, False)
          Else
            MyCell.Offset(0, -1).Value = WorksheetFunction.VLookup(Val(Mid(MyCell, 2, 3)), Range("LURNG"), 2, False)
          End If
        End If
    Next
End Sub

HTH

rylo

Hi

Which range? The lookup? The output?

rylo

Sory,

I mean the LURng. I've tried to put it in other worksheet, but there is error. What changes that I need to make in the code?

Thanks.

Hi

Fully qualify the name. So if you put it on sheet2, then try

Sheets("sheet2").Range("LURNG")

rylo

Got it! Thanks rylo!

Thank you for remind me..