Expert: Jan Karel Pieterse - 11/6/2009

Question
I am trying to write a formula in VBA to update the value of a specific cell based on its value in relation to a set of other cells. This is the formula I am trying to use, but i cannot seem to get it to work. I haven't done much in VBA so I am not sure if I am using the correct approach.
Public Function FF(Commissions)
Cell.Formula = R14
   If (N24 < Y12) = True Then R14 = U10
Else
   If (N24 > W12 And N24 < Y12) = True Then R14 = U12
Else
   If (N24 > W14 And N24 < Y14) = True Then R14 = U14
Else
   If (N24 > W16 And N24 < Y16) = True Then R14 = U16
Else
   If (N24 > W18 And N24 < Y18) = True Then R14 = U18
Else
   If (N24 > W20 And N24 < Y20) = True Then R14 = U20
Else
   If (N24 > W22) = True Then R14 = U22
End Function


Answer
Not sure what your requirements are, the proper syntat inside the function might be:

Public Function FF(Commissions)
   If (Range("N24") < Range("Y12")) Then
       Range("R14") = Range("U10")
   ElseIf (Range("N24") > Range("W12") And Range("N24") < Y12) Then
       Range("R14") = Range("U12")
   ElseIf (Range("N24") > Range("W14") And Range("N24") < Y14) Then
       Range("R14") = Range("U14")
   ElseIf (Range("N24") > Range("W16") And Range("N24") < Y16) Then
       Range("R14") = Range("U16")
   ElseIf (Range("N24") > Range("W18") And Range("N24") < Y18) Then
       Range("R14") = Range("U18")
   ElseIf (Range("N24") > Range("W20") And Range("N24") < Y20) Then
       Range("R14") = Range("U20")
   ElseIf (Range("N24") > Range("W22")) Then
       Range("R14") = Range("U22")
   End If
End Function

But you have an argument in the function that isn't used and the function uses a lot of hard-wired cell addresses. If this is a function that you want to use in a cell, then I advise you to pass all cells needed for the calculation into the function.