	AllExperts > Excel
	Search

About Aidan Heritage
Expertise
I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards

Experience
My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

Education/Credentials
I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

You are here: Experts > Computing/Technology > Microsoft Software > Excel > VBA Formula

Excel - VBA Formula

Expert: Aidan Heritage - 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
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

Add to this Answer Ask a Question

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here