Using Vlookup in Loops of differing sizes

TheRedCardinal

Board Regular
Joined
Jul 11, 2019
Messages
169
Hi all - disclaimer - I am trying to learn VBA and have taken on a project way too big for my experience! :cool:
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:

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 :)
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
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

Board Regular
Joined
Jul 11, 2019
Messages
169
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
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
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,177
Messages
5,640,618
Members
417,158
Latest member
jimmy1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top