Error Code 6: Overflow

Just when I thought I had it!!!

Here is my code:

Private Sub Tabulate()

' Keyboard Shortcut: Ctrl+x


Dim WSDSD As Worksheet
Set WSDSD = Worksheets("Data SD")
Dim WSRep As Worksheet
Set WSRep = Worksheets("Report")
Dim WSCri As Worksheet
Set WSCri = Worksheets("Criteria")

Dim Monthrange As Range
Set Monthrange = ThisWorkbook.Names("Monthrange").RefersToRange
Dim Yearrange As Range
Set Yearrange = ThisWorkbook.Names("Yearrange").RefersToRange
Dim Locrange As Range
Set Locrange = ThisWorkbook.Names("Locrange").RefersToRange
Dim Typerange As Range
Set Typerange = ThisWorkbook.Names("Typerange").RefersToRange
Dim Counrange As Range
Set Counrange = ThisWorkbook.Names("Counrange").RefersToRange

Dim ProVa As String
ProVa = WSCri.Range("C3").Value
Dim YrVa As String
YrVa = WSCri.Range("C10").Value
Dim MthVa As String
MthVa = WSCri.Range("C11").Value
Dim LocVa As Range
Set LocVa = WSCri.Range("C23")
Dim UnVa As Range
Set UnVa = WSCri.Range("C28")
Dim TyVa As Range
Set TyVa = WSCri.Range("C32")

Dim MRRCYTo As Range
Set MRRCYTo = WSRep.Range("D7")

Dim MRRrange As Range
Set MRRrange = ThisWorkbook.Names("MRRrange").RefersToRange

'When the scope is District
If ProVa = 0 And LocVa.Value = "" Then
MRRCYTo.Value =
WSDSD.Evaluate("=SUMPRODUCT((MRRrange<200000)*(MRRrange<>0)*(Typerange=""" &
TyVa.Value & """)*(Yearrange=" & YrVa & ")*(Monthrange=" & MthVa & "))") /
WSDSD.Evaluate("=SUMPRODUCT((MRRrange>1)*(Typerange=""" & TyVa.Value &
""")*(Yearrange=" & YrVa & ")*(Monthrange=" & MthVa & "))")
End If

End Sub


Ok....when I change the reference cell of TyVa and run the subroutine again,
I get the overflow error?



--
Thanks
Shawn

The overflow is caused by the contents of the ranges / cells you refer to.
Assuming the code is logically correct it is impossible to resolve your
problem here. Check the logic and the math based on cell contents if
necessary do a manual calculation. If the condition causing the overflow is
likely to arise then you need some error trapping and / or conditional logic
to prevent the problem.

--
Cheers
Nigel



"Shawn" <[email protected]> wrote in message
news:[email protected]...
> Just when I thought I had it!!!
>
> Here is my code:
>
> Private Sub Tabulate()
>
> ' Keyboard Shortcut: Ctrl+x
>
>
> Dim WSDSD As Worksheet
> Set WSDSD = Worksheets("Data SD")
> Dim WSRep As Worksheet
> Set WSRep = Worksheets("Report")
> Dim WSCri As Worksheet
> Set WSCri = Worksheets("Criteria")
>
> Dim Monthrange As Range
> Set Monthrange = ThisWorkbook.Names("Monthrange").RefersToRange
> Dim Yearrange As Range
> Set Yearrange = ThisWorkbook.Names("Yearrange").RefersToRange
> Dim Locrange As Range
> Set Locrange = ThisWorkbook.Names("Locrange").RefersToRange
> Dim Typerange As Range
> Set Typerange = ThisWorkbook.Names("Typerange").RefersToRange
> Dim Counrange As Range
> Set Counrange = ThisWorkbook.Names("Counrange").RefersToRange
>
> Dim ProVa As String
> ProVa = WSCri.Range("C3").Value
> Dim YrVa As String
> YrVa = WSCri.Range("C10").Value
> Dim MthVa As String
> MthVa = WSCri.Range("C11").Value
> Dim LocVa As Range
> Set LocVa = WSCri.Range("C23")
> Dim UnVa As Range
> Set UnVa = WSCri.Range("C28")
> Dim TyVa As Range
> Set TyVa = WSCri.Range("C32")
>
> Dim MRRCYTo As Range
> Set MRRCYTo = WSRep.Range("D7")
>
> Dim MRRrange As Range
> Set MRRrange = ThisWorkbook.Names("MRRrange").RefersToRange
>
> 'When the scope is District
> If ProVa = 0 And LocVa.Value = "" Then
> MRRCYTo.Value =
> WSDSD.Evaluate("=SUMPRODUCT((MRRrange<200000)*(MRRrange<>0)*(Typerange="""
&
> TyVa.Value & """)*(Yearrange=" & YrVa & ")*(Monthrange=" & MthVa & "))") /
> WSDSD.Evaluate("=SUMPRODUCT((MRRrange>1)*(Typerange=""" & TyVa.Value &
> """)*(Yearrange=" & YrVa & ")*(Monthrange=" & MthVa & "))")
> End If
>
> End Sub
>
>
> Ok....when I change the reference cell of TyVa and run the subroutine
again,
> I get the overflow error?
>
>
>
> --
> Thanks
> Shawn

I disagree..

Overflow errors are typically generated in VBA when assigning
values to variables that dont have enough bits to hold the data.

e.g.

integer values to byte variables.
long values to integer variables.

most often this happens when you are "looping" row numbers
and use an integer variable..

once you get to row 32768 +1 the error will pop..
as an integer can only hold 16bit (2byte) data.




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Nigel wrote :

> The overflow is caused by the contents of the ranges / cells you
> refer to. Assuming the code is logically correct it is impossible to
> resolve your problem here. Check the logic and the math based on
> cell contents if necessary do a manual calculation. If the condition
> causing the overflow is likely to arise then you need some error
> trapping and / or conditional logic to prevent the problem.

I split the formula in half and assigned the answer to two cells. I then
made MRRTo equal to the product of those two cells. This works just fine.
I am uncertain why this resolved the overflow, though.

Also, do you see a better way for me to do what I am trying to do overall?


--
Thanks
Shawn


"Nigel" wrote:

> The overflow is caused by the contents of the ranges / cells you refer to.
> Assuming the code is logically correct it is impossible to resolve your
> problem here. Check the logic and the math based on cell contents if
> necessary do a manual calculation. If the condition causing the overflow is
> likely to arise then you need some error trapping and / or conditional logic
> to prevent the problem.
>
> --
> Cheers
> Nigel
>
>
>
> "Shawn" <[email protected]> wrote in message
> news:[email protected]...
> > Just when I thought I had it!!!
> >
> > Here is my code:
> >
> > Private Sub Tabulate()
> >
> > ' Keyboard Shortcut: Ctrl+x
> >
> >
> > Dim WSDSD As Worksheet
> > Set WSDSD = Worksheets("Data SD")
> > Dim WSRep As Worksheet
> > Set WSRep = Worksheets("Report")
> > Dim WSCri As Worksheet
> > Set WSCri = Worksheets("Criteria")
> >
> > Dim Monthrange As Range
> > Set Monthrange = ThisWorkbook.Names("Monthrange").RefersToRange
> > Dim Yearrange As Range
> > Set Yearrange = ThisWorkbook.Names("Yearrange").RefersToRange
> > Dim Locrange As Range
> > Set Locrange = ThisWorkbook.Names("Locrange").RefersToRange
> > Dim Typerange As Range
> > Set Typerange = ThisWorkbook.Names("Typerange").RefersToRange
> > Dim Counrange As Range
> > Set Counrange = ThisWorkbook.Names("Counrange").RefersToRange
> >
> > Dim ProVa As String
> > ProVa = WSCri.Range("C3").Value
> > Dim YrVa As String
> > YrVa = WSCri.Range("C10").Value
> > Dim MthVa As String
> > MthVa = WSCri.Range("C11").Value
> > Dim LocVa As Range
> > Set LocVa = WSCri.Range("C23")
> > Dim UnVa As Range
> > Set UnVa = WSCri.Range("C28")
> > Dim TyVa As Range
> > Set TyVa = WSCri.Range("C32")
> >
> > Dim MRRCYTo As Range
> > Set MRRCYTo = WSRep.Range("D7")
> >
> > Dim MRRrange As Range
> > Set MRRrange = ThisWorkbook.Names("MRRrange").RefersToRange
> >
> > 'When the scope is District
> > If ProVa = 0 And LocVa.Value = "" Then
> > MRRCYTo.Value =
> > WSDSD.Evaluate("=SUMPRODUCT((MRRrange<200000)*(MRRrange<>0)*(Typerange="""
> &
> > TyVa.Value & """)*(Yearrange=" & YrVa & ")*(Monthrange=" & MthVa & "))") /
> > WSDSD.Evaluate("=SUMPRODUCT((MRRrange>1)*(Typerange=""" & TyVa.Value &
> > """)*(Yearrange=" & YrVa & ")*(Monthrange=" & MthVa & "))")
> > End If
> >
> > End Sub
> >
> >
> > Ok....when I change the reference cell of TyVa and run the subroutine
> again,
> > I get the overflow error?
> >
> >
> >
> > --
> > Thanks
> > Shawn
>
>
>

I split the formula in half and assigned the answer to two cells. I then
made MRRTo equal to the product of those two cells. This works just fine.
I am uncertain why this resolved the overflow, though.

Also, do you see a better way for me to do what I am trying to do overall?


--
Thanks
Shawn


"keepITcool" wrote:

> I disagree..
>
> Overflow errors are typically generated in VBA when assigning
> values to variables that dont have enough bits to hold the data.
>
> e.g.
>
> integer values to byte variables.
> long values to integer variables.
>
> most often this happens when you are "looping" row numbers
> and use an integer variable..
>
> once you get to row 32768 +1 the error will pop..
> as an integer can only hold 16bit (2byte) data.
>
>
>
>
> --
> keepITcool
> | www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> Nigel wrote :
>
> > The overflow is caused by the contents of the ranges / cells you
> > refer to. Assuming the code is logically correct it is impossible to
> > resolve your problem here. Check the logic and the math based on
> > cell contents if necessary do a manual calculation. If the condition
> > causing the overflow is likely to arise then you need some error
> > trapping and / or conditional logic to prevent the problem.
>

Most of the range assignments you make are never used, so you can omit those
you don't use.

--
Regards,
Tom Ogilvy


"Shawn" <[email protected]> wrote in message
news:[email protected]...
> I split the formula in half and assigned the answer to two cells. I then
> made MRRTo equal to the product of those two cells. This works just
fine.
> I am uncertain why this resolved the overflow, though.
>
> Also, do you see a better way for me to do what I am trying to do overall?
>
>
> --
> Thanks
> Shawn
>
>
> "Nigel" wrote:
>
> > The overflow is caused by the contents of the ranges / cells you refer
to.
> > Assuming the code is logically correct it is impossible to resolve your
> > problem here. Check the logic and the math based on cell contents if
> > necessary do a manual calculation. If the condition causing the
overflow is
> > likely to arise then you need some error trapping and / or conditional
logic
> > to prevent the problem.
> >
> > --
> > Cheers
> > Nigel
> >
> >
> >
> > "Shawn" <[email protected]> wrote in message
> > news:[email protected]...
> > > Just when I thought I had it!!!
> > >
> > > Here is my code:
> > >
> > > Private Sub Tabulate()
> > >
> > > ' Keyboard Shortcut: Ctrl+x
> > >
> > >
> > > Dim WSDSD As Worksheet
> > > Set WSDSD = Worksheets("Data SD")
> > > Dim WSRep As Worksheet
> > > Set WSRep = Worksheets("Report")
> > > Dim WSCri As Worksheet
> > > Set WSCri = Worksheets("Criteria")
> > >
> > > Dim Monthrange As Range
> > > Set Monthrange = ThisWorkbook.Names("Monthrange").RefersToRange
> > > Dim Yearrange As Range
> > > Set Yearrange = ThisWorkbook.Names("Yearrange").RefersToRange
> > > Dim Locrange As Range
> > > Set Locrange = ThisWorkbook.Names("Locrange").RefersToRange
> > > Dim Typerange As Range
> > > Set Typerange = ThisWorkbook.Names("Typerange").RefersToRange
> > > Dim Counrange As Range
> > > Set Counrange = ThisWorkbook.Names("Counrange").RefersToRange
> > >
> > > Dim ProVa As String
> > > ProVa = WSCri.Range("C3").Value
> > > Dim YrVa As String
> > > YrVa = WSCri.Range("C10").Value
> > > Dim MthVa As String
> > > MthVa = WSCri.Range("C11").Value
> > > Dim LocVa As Range
> > > Set LocVa = WSCri.Range("C23")
> > > Dim UnVa As Range
> > > Set UnVa = WSCri.Range("C28")
> > > Dim TyVa As Range
> > > Set TyVa = WSCri.Range("C32")
> > >
> > > Dim MRRCYTo As Range
> > > Set MRRCYTo = WSRep.Range("D7")
> > >
> > > Dim MRRrange As Range
> > > Set MRRrange = ThisWorkbook.Names("MRRrange").RefersToRange
> > >
> > > 'When the scope is District
> > > If ProVa = 0 And LocVa.Value = "" Then
> > > MRRCYTo.Value =
> > >
WSDSD.Evaluate("=SUMPRODUCT((MRRrange<200000)*(MRRrange<>0)*(Typerange="""
> > &
> > > TyVa.Value & """)*(Yearrange=" & YrVa & ")*(Monthrange=" & MthVa &
"))") /
> > > WSDSD.Evaluate("=SUMPRODUCT((MRRrange>1)*(Typerange=""" & TyVa.Value &
> > > """)*(Yearrange=" & YrVa & ")*(Monthrange=" & MthVa & "))")
> > > End If
> > >
> > > End Sub
> > >
> > >
> > > Ok....when I change the reference cell of TyVa and run the subroutine
> > again,
> > > I get the overflow error?
> > >
> > >
> > >
> > > --
> > > Thanks
> > > Shawn
> >
> >
> >

Hi keepITcool,
You are right of course, but my assertion was that the values in the cells
being assigned causes the error to arise in VBA. There would be no overflow
if the values remain within the scope of the type declaration.

--
Cheers
Nigel



"keepITcool" <[email protected]> wrote in message
news:[email protected]...
> I disagree..
>
> Overflow errors are typically generated in VBA when assigning
> values to variables that dont have enough bits to hold the data.
>
> e.g.
>
> integer values to byte variables.
> long values to integer variables.
>
> most often this happens when you are "looping" row numbers
> and use an integer variable..
>
> once you get to row 32768 +1 the error will pop..
> as an integer can only hold 16bit (2byte) data.
>
>
>
>
> --
> keepITcool
> | www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> Nigel wrote :
>
> > The overflow is caused by the contents of the ranges / cells you
> > refer to. Assuming the code is logically correct it is impossible to
> > resolve your problem here. Check the logic and the math based on
> > cell contents if necessary do a manual calculation. If the condition
> > causing the overflow is likely to arise then you need some error
> > trapping and / or conditional logic to prevent the problem.