Setting Variable with VLookup & Named Ranges

Question

I can not get the VLookup function to work when using a named range. I am sure it has something to do with how I am referencing "COA_Range" but cant find a solution that works

I have tried [], ([]), (""), [""],([""])......

(Below is an updated and expanded section of the code)

If Transaction_Type = 1 Then
    Debug.Print "Transaction Type :"; Transaction_Type
    Range("n10").Value = "Income"

    Debug.Print "COA # = "; TransactionInfo.Income_COA_Number.Value
    COA_Number = TransactionInfo.Income_COA_Number.Value
    Debug.Print COA_Number

    Range("n12").Value = TransactionInfo.Income_COA_Number.Value

    'thought from STACK OVERFLOW
    Debug.Print Range("COA_Range").Address()

    COA_1 = Application.WorksheetFunction.VLookup(COA_Number, Range("COA_Range"), 2, False)
    Debug.Print COA_1
    Range("n13").Value = COA_1

What's TransactionInfo.Income_COA_Number? You can also refer named range by Thisworkbook.Names("COA_Range").RefersToRange. — PatricK
– PatricK, Commented Mar 23, 2017 at 0:01
it is a value taken from a userform. TransactionInfo.Income_COA_Number.Value works throughout the rest of my macro. your "thisworkbook.names..." was unsuccessful — Reed Turgeon
– Reed Turgeon, Commented Mar 23, 2017 at 0:18
Try adding Debug.Print Range("COA_Range").Address() to make sure the range is found. — Tim Williams
– Tim Williams, Commented Mar 23, 2017 at 0:23
Yes the range is being found Range().Address() --> immediate --> $B$8:$F$41 — Reed Turgeon
– Reed Turgeon, Commented Mar 23, 2017 at 0:35
All of your test are ambiguous as to whether TransactionInfo.Income_COA_Number.Value is a true number or text-that-looks-like-a-number. I suspect it is the latter and the values in column B of Range("COA_Range") are true numbers. VLOOKUP cannot find text-that-looks-like-a-number within a column of true numebers. Convert with CLng(TransactionInfo.Income_COA_Number.Value2) to get a true number. Of course, if COA_Number had been declared as a long instead of not-at-all or a variant then this would have been immediately obvious. — user4039065
– user4039065, Commented Mar 23, 2017 at 1:13

marc_s · Accepted Answer · 2023-03-04 16:44:35Z

1

Following @Jeeped comment, make sure that the value in your User_Form named "TransactionInfo" in the TextBox "Income_COA_Number" has a Numeric value, and so all the values in your Range("COA_Range") cells.

I've added 2 optional solutions (pick the one you prefer):

Using Application.Match.
Using Find.

Code

Option Explicit
    
Sub VLookUpNamedRange()

Dim ws                  As Worksheet
Dim Transaction_Type    As Long
Dim MyCOARng            As Range
Dim COA_1               As Variant
Dim COA_Number          As Long
Dim FndRng              As Range

Set ws = Worksheets("Sheet7") '<-- modify "Sheet7" to your sheet's name    
Set MyCOARng = ws.Range("COA_Range") '<-- set Range to "COA_Range" Named Range

COA_Number = TransactionInfo.Income_COA_Number.Value
 
' === Option 1: Use Application.Match ===
If Not IsError(Application.VLookup(COA_Number, MyCOARng, 2, False)) Then ' <-- VLookup Successful
    COA_1 = Application.VLookup(COA_Number, MyCOARng, 2, False)
Else ' <-- VLookup failed
    COA_1 = COA_Number & " Not found in 'COA_Range' "
End If
    
' === Option 2: Use Find ===
Set FndRng = MyCOARng.Find(What:=COA_Number, LookIn:=xlValues, lookat:=xlWhole)
If Not FndRng Is Nothing Then '<-- successful find
    COA_1 = FndRng.Offset(, 2).Value
Else '<-- not found in your range
    COA_1 = COA_Number & " Not found in 'COA_Range' "
End If
 
Debug.Print COA_1 ' <-- for DEBUG Only

End Sub

edited Mar 4, 2023 at 16:44

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Mar 23, 2017 at 7:11

Shai Rado

33.7k7 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Reed Turgeon Over a year ago

Thank you for the response! I have posted my exact solution below

jainashish · Accepted Answer · 2017-03-23 01:29:56Z

0

Try to use Application.Vlookup instead of using Application.WorksheetFunction.Vlookup. Then set a Variant equal to this and if no match is found then it would return Error 2042 which can be tested using IsError

See the Example Code below:

Dim ws As Worksheet: Set ws = ActiveSheet            'Change the sheet reference appropriately
Dim rngLookUpTable As Range: Set rngLookUpTable = ws.Range("COA_Range")    
Dim vReturn As Variant

If Transaction_Type = 1 Then
    Range("N10").Value = "Income"
    COA_Number = TransactionInfo.Income_COA_Number.Value
    Range("N12").Value = TransactionInfo.Income_COA_Number.Value
    vReturn = Application.VLookup(COA_Number,rngLookUpTable, 2, False)
    Range("N13").Value = IIF(IsError(vReturn),"Not Found",vReturn)
End If

WorksheetFunction version of VLookup and Match need error handling that re-routes your code to an error handler, returns to the next statement to evaluate, etc. With the Application functions, you can avoid that mess.

answered Mar 23, 2017 at 1:29

jainashish

5,2717 gold badges41 silver badges52 bronze badges

1 Comment

Reed Turgeon Over a year ago

Thank you for the response! I have posted my exact solution below

Reed Turgeon · Accepted Answer · 2017-03-23 23:23:52Z

Special thanks to @jainashish, @Shai Rado for the thoughtful responses. I was able to pick up a few pointers from each.

It was @Jeeped however who actually solved my problem. The "number" was being read as text and the CLng() expression worked for me. I have added my updated code below.

    If Transaction_Type = 1 Then
    Debug.Print "Transaction Type :"; Transaction_Type
        Range("n10").Value = "Income"

        'thought from STACKOVERFLOW
            'need to make sure that the number taken fromt the userform is ACTUALLY a number and not text that looks like a number
                'use CLng to convert

    Debug.Print "COA # = "; CLng(TransactionInfo.Income_COA_Number.Value)
        COA_Number = CLng(TransactionInfo.Income_COA_Number.Value)
            Debug.Print "COA # = "; COA_Number
        Range("n12").Value = COA_Number

            'thought from STACK OVERFLOW
                Debug.Print Range("COA_Range").Address()
                    'Yes the range is being found...
                        Dim COA_Range As Range
                        Set COA_Range = Range("COA_Range")
                            Debug.Print COA_Range.Address()

        COA_1 = WorksheetFunction.VLookup(COA_Number, Range("COA_Range"), 2, False)
                Debug.Print COA_1
                    Range("n13").Value = COA_1

        COA_2 = WorksheetFunction.VLookup(COA_Number, Range("COA_Range"), 3, False)
                Debug.Print COA_2
                    Range("n14").Value = COA_2

        COA_3 = WorksheetFunction.VLookup(COA_Number, Range("COA_Range"), 4, False)
                Debug.Print COA_3
                    Range("n15").Value = COA_3

        COA_4 = WorksheetFunction.VLookup(COA_Number, Range("COA_Range"), 5, False)
                Debug.Print COA_4
                    Range("n16").Value = COA_4enter code here

Collectives™ on Stack Overflow

Setting Variable with VLookup & Named Ranges

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related