Maxson
11-13-2006, 06:54 PM
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!!
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!!