Hi
The tier values have to be refered as
Tier1 = range("h743").value
this will get the value contained in that particular cell
you can also do
range("H743").select
activecell.value
On Sep 8, 2:57 pm, XpandeR <[EMAIL PROTECTED]> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
