vlookup loop for a string

[jassybun]

I am trying to edit a code I found online, but having an issue every results is "NOT FOUND!" which isn't possible - I think it might be because the value is a string?

Sub CategoryChanger()
Dim rng as Range
Dim r as Range
Dim result as String

'## Define a range to represent the cells over which you would like to iterate:
'## Modify as needed...

Set rng = Range("B2:B100")
'## Iterate each cell in the Range defined "rng"
For Each r in rng.Cells
On Error Resume Next
result = Application.WorksheetFunction.VLookup(r.Value, Worksheets("Lookup").Range("A2:B350"),2,False)

If Err.Number <> 0 Then result = "NOT FOUND!"
On Error GoTo 0

'## Print result in the cell 10 columns to right
'## Modify as needed

rng.Offset(0,10).Value = result

'Clear out the "result" value for the next iteration:
result = vbNullstring
Next
End Sub

 

[YasserKhalil]

Hello
Can you upload sample of the workbook with some of the desired output?

 

[jassybun]

also, it needs to be a dynamic range - how would I change the range to only go to the last row with a value? and I think I should used workbook instead of worksheet...

 

[YasserKhalil]

Try this code (I suppose the workbook 'lookup' is open)

Private Sub CommandButton1_Click()
    Dim rng        As Range
    Dim c          As Range
    Dim result      As String

    Set rng = Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
   
    For Each c In rng.Cells
        On Error Resume Next
            result = Application.WorksheetFunction.VLookup(c.Value, Workbooks("Lookup").Worksheets("Sheet1").Range("A2:B350"), 2, False)
            If Err.Number <> 0 Then result = "Not Found"
        On Error GoTo 0
       
        c.Offset(, 10).Value = result
        result = vbNullString
    Next c
End Sub

 

[jassybun]

They all still say "Not Found!"..I had Lookup open too.

 

[YasserKhalil]

It is working for me. Please try the file (but keep both files open)

 

[jassybun]

I am using MS Excel 2016...could that be why?

 

[YasserKhalil]

Can you follow the lines of code line by line using F8 and notice each variable in the code ..?

 

[YasserKhalil]

Try adding the extension of the file in that part

 Workbooks("Lookup.xlsx")

 

[jassybun]

yessssssssssssssss! you are a genius..thank you!

 

[YasserKhalil]

You're welcome. Glad I can offer little help