[Solved] [Solved]excel vba 2003 how to use vLookup with enums

[dwc309]

I'm trying to use vLookup to return a value that will allow the automatic placement of expenditures in the correct account (i.e. column). To do this I've created an enum

Enum BudgetItem
Redacted 1
Entertainment
Kickstarter
AutoIns
Food
Autos
Gas
Misc
House
EatingOut
Clothes
Vacation
ManCave
Redacted 2
End Enum

The vLookup is returning the correct value but I'm not currently able to convert the value returned to copy the value to the correct account.

The code is

For Each rMcell In rAmex
v = Application.VLookup(rMcell, shtDescriptionDataSheet.Range("c1:" & strDescriptionDataLastCell).Value, 2, False)

If IsError(v) Or IsEmpty(v) Then
Else
a = BudgetItem.v + 1 ' This gives the error 'Method or data member not found. + 1

' Copy the amount to the proper account column
ActiveCell.Offset(0, 2).Select ' The expenditure is located in this cell.
Selection.Copy
a = a + 2
ActiveCell.Offset(0, a).Select ' This is where the expenditure is to be copied.
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
Next

I've tried a number of other possibilities, none of which have worked. I've also done a lot of searches without any luck.

The enum is in module 1. I've tried making it public but shouldn't have to as I currently understand things (It didn't work anyway).

The rest of the code is in one of the sheets of the excel file and the work is being done in another sheet.

I want to do it this way because accounts come and go as life rolls on.

Thanks in advance for any help that is provided.

 

[shamimarita]

Hello,

I see what you’re trying to do, and you’re close, but there’s a misunderstanding in how Enums work in VBA and how to reference them dynamically.

What’s happening:

• v = Application.VLookup(...) returns the name of the BudgetItem as a string (like "Entertainment"), not the Enum value itself.
• You’re then trying a = BudgetItem.v + 1, but BudgetItem.v doesn’t work because v is a variable containing the string, not an actual Enum member.

How to fix:
You need a way to map the string returned by VLOOKUP back to its Enum value. VBA does not provide a built-in way to do this dynamically. You have a couple of options:

Option 1: Use a Mapping Function

Add a function in your module to convert the name to its Enum value:

Public Function BudgetItemIndex(itemName As String) As Integer
    Select Case itemName
        Case "Redacted 1": BudgetItemIndex = BudgetItem.Redacted1
        Case "Entertainment": BudgetItemIndex = BudgetItem.Entertainment
        Case "Kickstarter": BudgetItemIndex = BudgetItem.Kickstarter
        Case "AutoIns": BudgetItemIndex = BudgetItem.AutoIns
        ' ...and so on for all enum members
        Case Else: BudgetItemIndex = -1 ' Not found
    End Select
End Function

Then, in your code:

Dim idx As Integer
idx = BudgetItemIndex(v)
If idx <> -1 Then
    a = idx + 1
    ' The rest of your code...
End If

Option 2: Use the Column Index Directly

Alternatively, if your accounts can change and you want a more dynamic solution, consider putting the account names as headers in your data sheet and using MATCH to get the column number:

Dim accountCol As Variant
accountCol = Application.Match(v, shtData.Rows(1), 0)
If Not IsError(accountCol) Then
    ActiveCell.Offset(0, accountCol - 1).Value = [amount to copy]
End If

This way, you don’t have to maintain an Enum at all, and your code automatically adapts to new or removed accounts.

 

[dwc309]

That is just awesome!!!! Thankyou very much for your help. Two solutions and I like the second one best.

Now. How do I mark this solved?

 

[shamimarita]

You are most welcome. Glad to hear that the solution worked. Keep exploring Excel with ExcelDemy and help each other to make the community stronger.

 

[dwc309]

You are most welcome. Glad to hear that the solution worked. Keep exploring Excel with ExcelDemy and help each other to make the community stronger.

Actually when I went to the details of what I wanted for the match because there is a set of accounts for each month of the year on this sheet. So when I write the match command, it is always returning a 2042 error. Here is the code

For Each rMcell In rAmex
        '   From the descriptor data sheet get the account name for this description... if there is one.
        v = Application.VLookup(rMcell, shtDescriptionDataSheet.Range("c1:" & _ sDescriptionDataLastCell).Value, 2, False)

        If IsError(v) Or IsEmpty(v) Then
        Else
            '   Find the correct account for this amount
            sTempStart = sConvertColumnNumberToAscii(ActiveCell.Column) & Trim(str(ActiveCell.Row))
            sTempEnd = sConvertColumnNumberToAscii(ActiveCell.Column + iNumAccts - 1) & Trim(str(ActiveCell.Row))
            sTempRange = sTempStart & ":" & sTempEnd
            vAccountCol = Application.Match(v, Range(sTempRange).Offset(-2, 3), 0)

            '   Copy the amount to the proper account column
            If Not IsError(vAccountCol) Then
                ActiveCell.Offset(0, vAccountCol - 1).Value = ActiveCell.Offset(0, 2).Value
            End If
        End If
    Next

I've tried just supplying "Entertainment" instead of v in the match statement. It didn't work.

I've tried verifying that the offset statement is correct by inserting a select statement to verify that the accounts are selected. That worked.

I've tried making v into a string and passing the string that is returned by the Vlookup statement and that didn't work. When this was tried, the web site indicated that Excel 2003 is very picky about it's types.

I see that the 0 for the third input parameter requires an exact match except that caps aren't matched.

I've checked for extra spaces on both sides of the account names in the cells.

Thank you.

 

[shamimarita]

Thanks for the detailed update! The Error 2042 from the Application.Match usually means the value wasn't found. A few quick checks:

• Ensure v is a string: v = CStr(v)
• Use Debug.Print "|" & v & "|" and compare with each header to spot hidden spaces or characters.
• Range(sTempRange).Offset(-2, 3) might not point to the expected headers—double-check with Debug.Print Range(...).Address.
• Try a hardcoded test: vAccountCol = Application.Match("Entertainment", Range(...), 0)

If that fails, it's likely a range issue.