Hi Jim,
you have to use the Val function as it returns the numbers contained
in a string as a numeric value of appropriate type.
For example:
Tier1 = Val(Range("$H$743"))
or that way I prefer this method:
Tier1 = Val(Cells(743, 8).Value (the 8 represents the H as the 8th
letter in alphabet.)
Good luck.
On Sep 7, 1:43 pm, Jim <[EMAIL PROTECTED]> 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
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
