Create Sub routine that translates values

[wwconslt]

We’re using a complicated macro to clean and format data into several sheets. I’d like to add the following functionality to the existing macro using vba. I’d like to accomplish this functionality solely in the VBA code and not have to use another sheet or copy additional columns and rows with the values. I would like to accomplish this using and IF THEN ELSE statement in the vba code.

IF B2 = “Giving” and D2 = “general fund” then D2 changes to 0501
ELSE IF
B2 = “Giving” and D2 = “global fund” then D2 becomes 0502
ELSE IF
B2=”Giving” and D2 =”” then D2 becomes 0501
ELSE IF
B2 =”Athletics” and D2 = “Friends of Athletics” then D2 becomes 0601
ELSE IF
B2 =”Athletics” and D2 = “” then D2 becomes “”
Etc.
I’ll have about 20 different options I’ll add.

Thanks for your help.

[CK76]

Without full list and/or sample workbook, can't give you exact code.

But I'd imagine, you could first start by creating dictionary/collection/array to store these conditions and use lookup operation in code to change destination value.

Though, I'd recommend storing lookup table elsewhere, to simplify and speed up process.

[wwconslt]

Thanks CK76. I'm trying to avoid having a user bring in a sheet that has the lookup table/array. Where/how could I store the lookup table?

[CK76]

Simple example using dictionary.

Sub Demo()
Dim dic As Object

Set dic = CreateObject("Scripting.Dictionary")
With dic
    .Add "Giving,general fund", "0501"
    .Add "Giving,global fund", "0502"
    .Add "Giving,", "0501"
    .Add "Athletics,Friends of Athletics", "0601"
    'more Key, Item pair
End With

[D2].Value = dic([B2].Value & "," & [D2].Value)

End Sub