Formatting Currencies based on a table with VBA

Question

So I want to apply number formatting to the value in the cells in column "AC" based on the currency denoted in column "O" by doing a vlookup on a named range I have on a different sheet (named "currencies")

Here is the code in question that is throwing up errors about being "unable to get the VLookup Property of the WorksheetFunction class"

If Not (Range("O" & i)) = vbNullString Then
    If Not IsEmpty(Range("O" & i)) Then
        Range("AC" & i).NumberFormat = 
        Application.WorksheetFunction.VLookup(Range("O" & i), currencies, 2, False)
    End If
End If

and here is the table that comprises the named range "currencies"

Anyone have any suggestions for why this isn't working and ways I can make it more resilient to throwing up errors that the people who will actually be using this macro wont understand?

Did you try with a error handler as in https://stackoverflow.com/questions/19280477/unable-to-get-the-vlookup-property-of-the-worksheetfunction-class-error ? This error number is quite common with the Vlookup in VBA — Pierre44, Aug 27 '18 at 07:53
Are you sure that the value being looked up (Range("0" & i)) is present in the Currencies named range in the first column? Also try Application.WorksheetFunction.VLookup(Range("O" & trim(Str(i))), currencies, 2, False) as I am not sure how have you defined variable i. — Bharath Raja, Aug 27 '18 at 08:00
As @BharathRaja implies, that error is usually because the value in `Oi` does not match any value in column 1 of `currencies`. This is often due to extraneous non-printing characters (`space` , `NBSP`) or can be due to a reference not pointing to where you think it is (worksheet not defined or incorrectly defined). Put in a breakpoint and step through the code, checking your variables at each step to see what they are returning. — Ron Rosenfeld, Aug 27 '18 at 10:00
Change `Application.Worksheetfunction.Vlookup` into `Application.Vlookup` to be able to handle any error it may throw at you. Using `Worksheetfunction` will actually stop your code before going into debug-mode. The latter will not stop your code before going into debug-mode and will return the error as a variable, which you can start dealing with. — JvdV, Aug 27 '18 at 11:20

Lowpar · Answer 1 · 2018-08-27T09:25:44.713

Here I would use the evaluate function, because it facilitates writing more complex formulas in vba. Now because of the IFERROR there will be no error just a blank cell if the formula does not work. Once a value is retrieved by the evaluate function, then apply your formatting.

If Not (Range("O" & i)) = vbNullString Then
    If Not IsEmpty(Range("O" & i)) Then
        If Evaluate("IFERROR(VLOOKUP(O" & i & ",currencies, 2, False),"""")") <> "" then
          'APPLY NUMBER FORMATTING HERE
           Range("AC" & i).Numberformat = ""
        End if
    End If
End If

Based on previous questions you have asked, I imagine your code should look like this:

If Not (Range("O" & i)) = vbNullString Then
    If Not IsEmpty(Range("O" & i)) Then
        If Evaluate("IFERROR(VLOOKUP(O" & i & ",currencies, 2, False),"""")") <> "" then
          'APPLY NUMBER FORMATTING HERE
           Select Case ("IFERROR(VLOOKUP(O" & i & ",currencies, 2, False),"""")")
           Case "USD"
           Range("AC" & i).NumberFormat = "$#,##0.00_);($#,##0.00)"
           Case "RMB"
           Range("AC" & i).NumberFormat = "[$¥-zh-CN]#,##0.00;[$¥-zh-CN]-#,##0.00"
           Case "EUR"
           Range("AC" & i).NumberFormat = "[$€-x-euro2] #,##0.00_);([$€-x-euro2] #,##0.00)"
           Case "GBP"
           Range("AC" & i).NumberFormat = "[$£-en-GB]#,##0.00;-[$£-en-GB]#,##0.00"
           Case "HKD"
           Range("AC" & i).NumberFormat = "[$HK$-zh-HK]#,##0.00_);([$HK$-zh-HK]#,##0.00)"
           Case "JPY"
           Range("AC" & i).NumberFormat = "[$¥-ja-JP]#,##0.00;-[$¥-ja-JP]#,##0.00"
           End Select
        End if
    End If
End If

Formatting Currencies based on a table with VBA

1 Answers1