Using Vlookup in Loops of differing sizes

[TheRedCardinal]

Hi all - disclaimer - I am trying to learn VBA and have taken on a project way too big for my experience!
I am trying to build up the elements one by one.

My current task is this:

• Report from my Source data has country codes using 3 letters
• I need to update this to the two letter ISO code
• We currently have a list of the conversions in a 2 column table
• Each period the user inserts a row, and creates a Vlookup formula for the 3 digit code in the set table

I want to remove those two steps.

Currently my macro:

• Starts with the prior period final report
• Blanks the worksheet called "Import Data"
• Prompts the user to select the raw data file from a Dialog Box
• Copies the worksheet in the file into my workbook and stores it in the "Import Data" sheet

I now want to perform several checks and updates on the data, and this is the first of them.

I have been researching and I have composed the following code:

Sub CountryLookup()


'Find how many rows in range


    Dim lRow As Long
    
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    
'Create loop


    Dim ThisWB As Workbook, ThisWS As Worksheet, CellA As Range
    
    Set ThisWB = ThisWorkbook
    Set ThisWS = ThisWB.Sheets("Data from VG")


    With ThisWS
        For Each CellA In .Range("C2:C" & lRow)
            .Cells(CellA.Row, 3) = Application.WorksheetFunction.VLookup(CellA.Value, "D:E", 2, 0)
        Next CellA
    End With
        
Range("B2:B25").Formula = "=Vlookup(A2,D$3:E$26,2,0)"
    
End Sub

Some notes:

• This is on a working file so the references are not the same as the finished product, I am just testing
• I think my lRow Variable works out how many rows are in my data
• Then my formula looks at the 3 digit code in Row A, performs a VLookup on the table in columns D:E, and returns the 2 digit code which is contained in column E (column 2) of that table - it puts it in the relevant row in column C
• It then loops through for every cell in the range

I have added the same formula in Column B so I can compare the results - but in the final version I don't want formulae just the result.

I get the 1004 error when I get to the WorkSheetFunction line.

I cannot emphasis how new I am to this VBA lark so please be gentle

 

[Fluff]

How about

Sub CountryLookup()


'Find how many rows in range


    Dim lRow As Long
    Dim Res As Variant
    
'Create loop


    Dim ThisWB As Workbook, ThisWS As Worksheet, CellA As Range
    
    Set ThisWB = ThisWorkbook
    Set ThisWS = ThisWB.Sheets("Data from VG")


    With ThisWS
        lRow = .Cells(Rows.Count, 1).End(xlUp).Row

        For Each CellA In .Range("A2:A" & lRow)
            Res = Application.VLookup(CellA.Value, .Range("D:E"), 2, 0)
            If Not IsError(Res) Then CellA.Offset(, 2) = Res
        Next CellA
    End With
        
Range("B2:B25").Formula = "=Vlookup(A2,D$3:E$26,2,0)"
    
End Sub

 

[TheRedCardinal]

Thank you I'll give it a go later.

Would you mind just explaining what the changes have done?

 

[TheRedCardinal]

Think I have this now - could somebody just confirm that what the new code does is:

- sets a new variable called Res
- Attaches a value to Res as being the result of the VLookup
- If this results in an error - do nothing
- If not an error, then it puts the value of Res in the cell 2 columns away (i.e. Looked up A2, posted result of Res in C2)

If possible though I'd love to know why mine didn't work - trying to learn as I go along!

Thanks so much this forum is amazing.

 

[Fluff]

You have understood it perfectly.

With your code you were setting lRow to the last used row on the active sheet, which may or may not have been sheet "Data from VG"
Also your Vlookup was wrong, you need to supply a range rather than the part in red

Application.[COLOR=#0000ff]WorksheetFunction[/COLOR].VLookup(CellA.Value, [COLOR=#ff0000]"D:E"[/COLOR], 2, 0)

also if the lookup value is not found in column D, your code would crash. Which is why I used a variable to check the result of the vlookup & removed the part in blue