CODEWITHSUNDEEP
excelvba
Grace
Grace

Reputation: 1

VBA custom IF function not working

I had the following IF statement:

lF(C:C=CE,0.3,(IF(C:C=CS,0.2,(IF( C:C=TE,0.2,(IF( C:C=MV,0.25,(IF( C:C=HCV,0.375,(IF( C:C=HM,0.3,(IF( C:CLM,0.125,(IF( C:C=FF,0.125,(IF( C:C=OE,0.3,0))))))))))

Which I then entered as a function in my first VBA module as:

Function DepRate(pVal As String) As Long

   If pVal = "HCV" Then
      DepRate = 0.375

   ElseIf pVal = "OE" Then
      DepRate = 0.3

   ElseIf pVal = "CE" Then
      DepRate = 0.3

   ElseIf pVal = "CS" Then
      DepRate = 0.2

   ElseIf pVal = "TE" Then
      DepRate = 0.2

   ElseIf pVal = "MV" Then
      DepRate = 0.25

   ElseIf pVal = "FF" Then
      DepRate = 0.125

   ElseIf pVal = "LM" Then
      DepRate = 0.125

   Else
      DepRate = 0
   End If

End Function

The result, when the function is used on excel, is always 0 even when the value in the particular cell is true.

How to correct this?

Upvotes: 0

Views: 759

Answers (1)

El Ronnoco
El Ronnoco

Reputation: 11912

This is a formula which refers to named ranges. So for example C:C = CE is comparing values in the column C to the named range CE.

So for example you need to have a function like this...

Function DepRate(pVal As String) As Long

   If pVal = ActiveSheet.Range("HCV").Value Then
      DepRate = 0.375
etc... etc..

You could also use a Select..Case as Veve says. But perhaps even better would be hold the values in a hidden sheet and then the lookups can be more easily configured.

I would be interested know what is in the named range CE for example? (Formulas > Name manager)

Upvotes: 1

Related Questions