Solved: VBA "IF" module

[Maxson]

I am trying to insert the following code into a spreadsheet, so that when the applicable data is selected from my dropdown list, the corresponding Code # will populate in the next cell over:

Function Max(pVal As String) As Long
If pVal = "OfficerCompensation" Then
CalcValue = "100-001"
ElseIf pVal = "Salaries" Then
CalcValue = "100-002"
ElseIf pVal = "BonusSigning" Then
CalcValue = "100-003"
ElseIf pVal = "BonusPerformance" Then
CalcValue = "100-004"
ElseIf pVal = "BonusRelease" Then
CalcValue = "100-005"
ElseIf pVal = "PayrollTaxes" Then
CalcValue = "100-006"
ElseIf pVal = "HealDentalIns" Then
CalcValue = "100-007"

ElseIf pVal = "Insurance" Then
CalcValue = "100-008"
ElseIf pVal = "InsuranceDisability" Then
CalcValue = "100-009"
ElseIf pVal = "InsuranceWorkersComp" Then
CalcValue = "100-010"
ElseIf pVal = "401kMatching" Then
CalcValue = "100-111"
ElseIf pVal = "TemporaryHelp" Then
CalcValue = "100-112"
ElseIf pVal = "PersonalServices" Then
CalcValue = "100-113"

ElseIf pVal = "Events" Then
CalcValue = "100-114"
ElseIf pVal = "EmployeeEduTraining" Then
CalcValue = "100-115"
ElseIf pVal = "Amortization" Then
CalcValue = "200-001"

ElseIf pVal = "TradeShows" Then
CalcValue = "300-001"
ElseIf pVal = "Misc" Then
CalcValue = "300-002"
ElseIf pVal = "Legal" Then
CalcValue = "400-001"
ElseIf pVal = "LegalIP" Then
CalcValue = "400-002"
ElseIf pVal = "Accounting" Then
CalcValue = "400-003"
ElseIf pVal = "OutsourcedHrHRIS" Then
CalcValue = "400-004"

ElseIf pVal = "OutsourcedHrBenefitsAdmin" Then
CalcValue = "400-005"
ElseIf pVal = "OutsourcedHrPayroll" Then
CalcValue = "400-006"
ElseIf pVal = "Consultants401k" Then
CalcValue = "400-007"

ElseIf pVal = "ConsultantsHR" Then
CalcValue = "400-008"

ElseIf pVal = "ConsultantsPtLeadsCFO" Then
CalcValue = "400-109"
ElseIf pVal = "Consultants" Then
CalcValue = "400-110"
ElseIf pVal = "ConsultantsPR" Then
CalcValue = "400-111"
ElseIf pVal = "ConsultantsPtLeadsEngine" Then
CalcValue = "400-012"
ElseIf pVal = "ConsultantsPtLeadsCTO" Then
CalcValue = "400-013"
ElseIf pVal = "PersonalServices" Then
CalcValue = "400-114"
ElseIf pVal = "Web" Then
CalcValue = "400-115"
ElseIf pVal = "Branding" Then
CalcValue = "400-116"

ElseIf pVal = "Video" Then
CalcValue = "400-117"
ElseIf pVal = "Business" Then
CalcValue = "400-118"
ElseIf pVal = "IT" Then
CalcValue = "400-119"

ElseIf pVal = "ConceptArt" Then
CalcValue = "400-120"
ElseIf pVal = "ModelingAnimation" Then
CalcValue = "400-121"
ElseIf pVal = "Domestic" Then
CalcValue = "500-001"
ElseIf pVal = "International" Then
CalcValue = "500-002"
ElseIf pVal = "AdvCommittee" Then
CalcValue = "500-003"

ElseIf pVal = "RecruitingFees" Then
CalcValue = "600-001"
ElseIf pVal = "RecruitingTravel" Then
CalcValue = "600-002"
ElseIf pVal = "RecruitingAdverts" Then
CalcValue = "600-003"
ElseIf pVal = "Relocation" Then
CalcValue = "600-004"

ElseIf pVal = "RelocationCorporateHousing" Then
CalcValue = "600-005"
ElseIf pVal = "RelocationMovingExpense" Then
CalcValue = "600-006"
ElseIf pVal = "CareerFairs" Then
CalcValue = "600-007"
ElseIf pVal = "InternalHeadHunting" Then
CalcValue = "600-008"

ElseIf pVal = "SoftwareExpense" Then
CalcValue = "700-101"
ElseIf pVal = "ConferencesMeetings" Then
CalcValue = "700-102"
ElseIf pVal = "OfficeExpense" Then
CalcValue = "700-103"
ElseIf pVal = "DuesSubscriptions" Then
CalcValue = "700-104"

ElseIf pVal = "Insurance" Then
CalcValue = "700-105"
ElseIf pVal = "SuppliesOffice" Then
CalcValue = "700-106"
ElseIf pVal = "SuppliesComputer" Then
CalcValue = "700-107"
ElseIf pVal = "SuppliesArt" Then
CalcValue = "700-108"

ElseIf pVal = "PayrollProcessing" Then
CalcValue = "700-109"
ElseIf pVal = "PostageDelivery" Then
CalcValue = "700-110"
ElseIf pVal = "PrintingReproduction" Then
CalcValue = "700-111"
ElseIf pVal = "LicensesPermitsVisas" Then
CalcValue = "700-112"

ElseIf pVal = "BankServiceCharges" Then
CalcValue = "700-113"
ElseIf pVal = "Contributions" Then
CalcValue = "700-114"
ElseIf pVal = "AutoExpense" Then
CalcValue = "700-115"
ElseIf pVal = "Other" Then
CalcValue = "700-116"

ElseIf pVal = "Gifts" Then
CalcValue = "700-117"
ElseIf pVal = "MealsEntertainment" Then
CalcValue = "700-118"
ElseIf pVal = "RentUtilities" Then
CalcValue = "800-101"
ElseIf pVal = "EquipmentRental" Then
CalcValue = "800-102"

ElseIf pVal = "TelephoneInternet" Then
CalcValue = "800-103"
ElseIf pVal = "Repairs" Then
CalcValue = "800-104"
ElseIf pVal = "RepairsBuilding" Then
CalcValue = "800-105"
ElseIf pVal = "RepairsComputer" Then
CalcValue = "800-106"
ElseIf pVal = "RepairsEquipment" Then
CalcValue = "800-107"

ElseIf pVal = "Utilities" Then
CalcValue = "800-108"
ElseIf pVal = "UtilitiesGasElectric" Then
CalcValue = "800-109"

ElseIf pVal = "MiscEquipment" Then
CalcValue = "800-110"
ElseIf pVal = "ITServiceEquipment" Then
CalcValue = "800-111"
ElseIf pVal = "DepreciationExpense" Then
CalcValue = "800-112"
ElseIf pVal = "CorporateApts" Then
CalcValue = "800-113"
ElseIf pVal = "OtherOperating" Then
CalcValue = "800-114"

ElseIf pVal = "FinanceCharge" Then
CalcValue = "900-101"
ElseIf pVal = "LoanInterest" Then
CalcValue = "900-102"

ElseIf pVal = "Federal" Then
CalcValue = "1000-101"
ElseIf pVal = "State" Then
CalcValue = "1000-102"
ElseIf pVal = "Local" Then
CalcValue = "1000-103"
ElseIf pVal = "LicensesPermits" Then
CalcValue = "1000-104"
ElseIf pVal = "OtherTaxes" Then
CalcValue = "1100-105"

Else
CalcValue = 0
End If
End Function


So in my excel sheet, if A12 has the value of "OtherTaxes", If I were to use the formula "=Max(A12)" in A13, I was expecting a return of "1100-105".

Instead I get "0". And a headache. I am a total beginner, but am trying!!! Any help will be appreciated!!

[Norie]

I'm not 100% sure what you are trying to do but I would strongly recommend that you don't use Max as the name for this UDF.

MAX is a native worksheet function, so I don't quite know how Excel will handle a UDF of the same name.

[johnske]

Also, get used to using a Select Case construct for cases like this e.g.
Option Explicit

Function MaxP(pVal As String) As String

Select Case pVal
Case "OfficerCompensation"
MaxP = "100-001"
Case "Salaries"
MaxP = "100-002"
Case "BonusSigning"
MaxP = "100-003"
Case "BonusPerformance"
MaxP = "100-004"
Case "BonusRelease"
MaxP = "100-005"
Case "PayrollTaxes"
MaxP = "100-006"
Case "HealDentalIns"
MaxP = "100-007"
Case "Insurance"
MaxP = "100-008"
Case "InsuranceDisability"
MaxP = "100-009"
Case "InsuranceWorkersComp"
MaxP = "100-010"
Case "401kMatching"
MaxP = "100-111"
Case "TemporaryHelp"
MaxP = "100-112"
Case "PersonalServices"
MaxP = "100-113"
Case "Events"
MaxP = "100-114"
Case "EmployeeEduTraining"
MaxP = "100-115"
Case "Amortization"
MaxP = "200-001"
Case "TradeShows"
MaxP = "300-001"
Case "Misc"
MaxP = "300-002"
Case "Legal"
MaxP = "400-001"
Case "LegalIP"
MaxP = "400-002"
Case "Accounting"
MaxP = "400-003"
Case "OutsourcedHrHRIS"
MaxP = "400-004"
Case "OutsourcedHrBenefitsAdmin"
MaxP = "400-005"
Case "OutsourcedHrPayroll"
MaxP = "400-006"
Case "Consultants401k"
MaxP = "400-007"
Case "ConsultantsHR"
MaxP = "400-008"
Case "ConsultantsPtLeadsCFO"
MaxP = "400-109"
Case "Consultants"
MaxP = "400-110"
Case "ConsultantsPR"
MaxP = "400-111"
Case "ConsultantsPtLeadsEngine"
MaxP = "400-012"
Case "ConsultantsPtLeadsCTO"
MaxP = "400-013"
Case "PersonalServices"
MaxP = "400-114"
Case "Web"
MaxP = "400-115"
Case "Branding"
MaxP = "400-116"
Case "Video"
MaxP = "400-117"
Case "Business"
MaxP = "400-118"
Case "IT"
MaxP = "400-119"
Case "ConceptArt"
MaxP = "400-120"
Case "ModelingAnimation"
MaxP = "400-121"
Case "Domestic"
MaxP = "500-001"
Case "International"
MaxP = "500-002"
Case "AdvCommittee"
MaxP = "500-003"
Case "RecruitingFees"
MaxP = "600-001"
Case "RecruitingTravel"
MaxP = "600-002"
Case "RecruitingAdverts"
MaxP = "600-003"
Case "Relocation"
MaxP = "600-004"
Case "RelocationCorporateHousing"
MaxP = "600-005"
Case "RelocationMovingExpense"
MaxP = "600-006"
Case "CareerFairs"
MaxP = "600-007"
Case "InternalHeadHunting"
MaxP = "600-008"
Case "SoftwareExpense"
MaxP = "700-101"
Case "ConferencesMeetings"
MaxP = "700-102"
Case "OfficeExpense"
MaxP = "700-103"
Case "DuesSubscriptions"
MaxP = "700-104"
Case "Insurance"
MaxP = "700-105"
Case "SuppliesOffice"
MaxP = "700-106"
Case "SuppliesComputer"
MaxP = "700-107"
Case "SuppliesArt"
MaxP = "700-108"
Case "PayrollProcessing"
MaxP = "700-109"
Case "PostageDelivery"
MaxP = "700-110"
Case "PrintingReproduction"
MaxP = "700-111"
Case "LicensesPermitsVisas"
MaxP = "700-112"
Case "BankServiceCharges"
MaxP = "700-113"
Case "Contributions"
MaxP = "700-114"
Case "AutoExpense"
MaxP = "700-115"
Case "Other"
MaxP = "700-116"
Case "Gifts"
MaxP = "700-117"
Case "MealsEntertainment"
MaxP = "700-118"
Case "RentUtilities"
MaxP = "800-101"
Case "EquipmentRental"
MaxP = "800-102"
Case "TelephoneInternet"
MaxP = "800-103"
Case "Repairs"
MaxP = "800-104"
Case "RepairsBuilding"
MaxP = "800-105"
Case "RepairsComputer"
MaxP = "800-106"
Case "RepairsEquipment"
MaxP = "800-107"
Case "Utilities"
MaxP = "800-108"
Case "UtilitiesGasElectric"
MaxP = "800-109"
Case "MiscEquipment"
MaxP = "800-110"
Case "ITServiceEquipment"
MaxP = "800-111"
Case "DepreciationExpense"
MaxP = "800-112"
Case "CorporateApts"
MaxP = "800-113"
Case "OtherOperating"
MaxP = "800-114"
Case "FinanceCharge"
MaxP = "900-101"
Case "LoanInterest"
MaxP = "900-102"
Case "Federal"
MaxP = "1000-101"
Case "State"
MaxP = "1000-102"
Case "Local"
MaxP = "1000-103"
Case "LicensesPermits"
MaxP = "1000-104"
Case "OtherTaxes"
MaxP = "1100-105"
Case Else
MaxP = 0
End Select
End Function

[mdmackillop]

so that when the applicable data is selected from my dropdown list, the corresponding Code # will populate in the next cell over
I'm not clear what you mean by this.

[Maxson]

Also, get used to using a Select Case construct for cases like this e.g.
Option Explicit

Function MaxP(pVal As String) As String

Select Case pVal
Case "OfficerCompensation"
MaxP = "100-001"
Case "Salaries"
MaxP = "100-002"
Case "BonusSigning"
MaxP = "100-003"
Case Else
MaxP = 0
End Select
End Function


Johnske!! Thank you so much! This works perfectly!!! I cannot begin to tell you how much you have helped me!

[Norie]

Maxson

I know you've got a solution for this but I was wondering why you are using a UDF in the first place.

As far as I can see this could easily be accomplished using worksheet functions, perhaps VLOOKUP.

To use that you would need to set up a table of some sort, but the advantage of that would be that any updates would be automatic.