Hello, I am new to VBA and I am trying to create a formula that will
reference my variable "AW" and based on the range of numbers it falls
between returns the formula you see listed in my IF...then
statements. Any help would be appreciated.
Right now, my function always returns a value of zero. Thanks.
Function AVGCASEVAR(Avg_Cases_Wk, AW)
' Calulates the variance in Cases of beer used per week versus an
average as determined by the appropiate tier.
Dim Tier1 As Long, Tier2 As Long
Dim Tier3 As Long, Tier4 As Long
Dim Tier5 As Long
Dim Avg_Cases_Week As Long
Tier1 = Range("$H$743")
Tier2 = Range("$H$744")
Tier3 = Range("$H$745")
Tier4 = Range("$H$746")
Tier5 = Range("$H$747")
If AW < 3000 Then AVGCASEVAR = Tier1 - Avg_Cases_Wk
[ElseIf AW > 3000 AND <= 4000 Then [AVGCASEVAR = Tier2 -
Avg_Cases_Wk]]
[ElseIf AW > 4000 AND <= 5000 Then [AVGCASEVAR = Tier3 -
Avg_Cases_Wk]]
[ElseIf AW > 5000 AND <= 6000 Then [AVGCASEVAR = Tier4
- Avg_Cases_Wk]]
[Else: [AVGCASEVAR = Tier5 - Avg_Cases_Wk]]
End Function
--~--~---------~--~----~------------~-------~--~----~
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
Visit & Join Our Orkut Community at
http://www.orkut.com/Community.aspx?cmm=22913620
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
-~----------~----~----~----~------~----~------~--~---
