Expert: Aidan Heritage - 11/7/2009

Question
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: As a function, it should have something stored in a variable called FF, and to VB you have a lot of undeclared variables - which SEEM to relate to fixed cells - so I THINK this should be a macro - but I'm not 100% sure what it's trying to do

Public sub FF()

Cell.Formula = R14 'what is this line
 If (range("N24") < Range("Y12"))  Then range("R14") = rang("U10")
'I've amended this line so you can see how it should look
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 sub

Hope this helps - but let me know if I can help further - my email if it helps is aidan.heritage@virgin.net

---------- FOLLOW-UP ----------

QUESTION: Sorry one last question, if my work book has multiple pages how do I get it to like to the cells on a specific page?

ANSWER: sheets("Whatever").range("Whatever")

is a way of referencing cells on different sheet - hope this is clear - again, if you need further help just ask - you have my email address now if it helps!

---------- FOLLOW-UP ----------

QUESTION: I think this should be my last question. I have everything working the way I want it to when I hit the run button, but now I need it to update automatically when any information is input into the work book and I am not sure what command I need to use to do that.

Answer
use the CHANGE event of the worksheet to handle the macro (vba editor, click the worksheet, change the dropdown from GENERAL to Worksheet - you should the see the change event. It is possible to restrict this to only fire when certain cells/rows/columns are changed which might be useful.