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)*(MRR range<0)*(Typerange=""" &
TyVa.Value & """)*(Yearrange=" & YrVa & ")*(Monthrange=" & MthVa & "))") /
WSDSD.Evaluate("=SUMPRODUCT((MRRrange1)*(Typerang e=""" & 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" wrote in message
...
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)*(MRR range<0)*(Typerange="""
&
TyVa.Value & """)*(Yearrange=" & YrVa & ")*(Monthrange=" & MthVa & "))") /
WSDSD.Evaluate("=SUMPRODUCT((MRRrange1)*(Typerang e=""" & 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" wrote in message
...
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)*(MRR range<0)*(Typerange="""
&
TyVa.Value & """)*(Yearrange=" & YrVa & ")*(Monthrange=" & MthVa & "))") /
WSDSD.Evaluate("=SUMPRODUCT((MRRrange1)*(Typerang e=""" & 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" wrote in message
...
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" wrote in message
...
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)*(MRR range<0)*(Typerange="""
&
TyVa.Value & """)*(Yearrange=" & YrVa & ")*(Monthrange=" & MthVa &
"))") /
WSDSD.Evaluate("=SUMPRODUCT((MRRrange1)*(Typerang e=""" & 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" wrote in message
.com...
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.