| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble referencing the correct cell. Function will not work.
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble referencing the correct cell. Function will not work.
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") select case AW case is < 3000: AVGCASEVAR = Tier1 - Avg_Cases_Wk case is <= 4000: AVGCASEVAR = Tier2 - Avg_Cases_Wk case is <= 5000: AVGCASEVAR = Tier3 - Avg_Cases_Wk case is <= 6000: AVGCASEVAR = Tier4 - Avg_Cases_Wk case else : AVGCASEVAR = Tier5 - Avg_Cases_Wk end select End Function "Jim" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble referencing the correct cell. Function will not work.
Your If..ElseIf..Else syntax is completely wrong. Here is the correct syntax
for that part of your code... If AW <= 3000 Then AVGCASEVAR = Tier1 - Avg_Cases_Wk ElseIf AW 3000 And AW <= 4000 Then AVGCASEVAR = Tier2 - Avg_Cases_Wk ElseIf AW 4000 And AW <= 5000 Then AVGCASEVAR = Tier3 - Avg_Cases_Wk ElseIf AW 5000 And AW <= 6000 Then AVGCASEVAR = Tier4 - Avg_Cases_Wk Else AVGCASEVAR = Tier5 - Avg_Cases_Wk End If However, given that VB processes the If, ElseIf and Else statements in the order they are listed in, you can take advantage of this fact in order to simplify the code further (you don't need to check each bottom range in the ElseIf as the preceding section would already have trapped that condition)... If AW <= 3000 Then AVGCASEVAR = Tier1 - Avg_Cases_Wk ElseIf AW <= 4000 Then AVGCASEVAR = Tier2 - Avg_Cases_Wk ElseIf AW <= 5000 Then AVGCASEVAR = Tier3 - Avg_Cases_Wk ElseIf AW <= 6000 Then AVGCASEVAR = Tier4 - Avg_Cases_Wk Else AVGCASEVAR = Tier5 - Avg_Cases_Wk End If -- Rick (MVP - Excel) "Jim" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble referencing the correct cell. Function will not work.
On Sep 7, 10:51*am, "Rick Rothstein"
wrote: Your If..ElseIf..Else syntax is completely wrong. Here is the correct syntax for that part of your code... * * If AW <= 3000 Then * * * AVGCASEVAR = Tier1 - Avg_Cases_Wk * * ElseIf AW 3000 And AW <= 4000 Then * * * AVGCASEVAR = Tier2 - Avg_Cases_Wk * * ElseIf AW 4000 And AW <= 5000 Then * * * AVGCASEVAR = Tier3 - Avg_Cases_Wk * * ElseIf AW 5000 And AW <= 6000 Then * * * AVGCASEVAR = Tier4 - Avg_Cases_Wk * * Else * * * AVGCASEVAR = Tier5 - Avg_Cases_Wk * * End If However, given that VB processes the If, ElseIf and Else statements in the order they are listed in, you can take advantage of this fact in order to simplify the code further (you don't need to check each bottom range in the ElseIf as the preceding section would already have trapped that condition)... * * If AW <= 3000 Then * * * AVGCASEVAR = Tier1 - Avg_Cases_Wk * * ElseIf *AW <= 4000 Then * * * AVGCASEVAR = Tier2 - Avg_Cases_Wk * * ElseIf *AW <= 5000 Then * * * AVGCASEVAR = Tier3 - Avg_Cases_Wk * * ElseIf *AW <= 6000 Then * * * AVGCASEVAR = Tier4 - Avg_Cases_Wk * * Else * * * AVGCASEVAR = Tier5 - Avg_Cases_Wk * * End If -- Rick (MVP - Excel) "Jim" wrote in message ... 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- Hide quoted text - - Show quoted text - Wanted to let you know I am still working on the issue. There was no time to work on it today, but these look like answers that should work. I will let you know. Thanks for the help! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Getting the correct reference cell to work | Excel Discussion (Misc queries) | |||
| If I have a work sheet protected and try to run a macro to hide rows or columns it won't work. Correct? | Excel Programming | |||
| Ok, here is the Correct equation I am having trouble with€¦ (from T | Excel Worksheet Functions | |||
| I want to correct a #div/0! error but the IF function doesnt work | Excel Worksheet Functions | |||
| referencing other work book using the INDIRECT function | Excel Programming | |||
![Jim[_73_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
