Trouble referencing the correct cell. Function will not work.

[Jim]

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

 

[Joel]

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

 

[Rick Rothstein]

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

 

[Jim]

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)

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!