+ Reply to Thread
Results 1 to 7 of 7

Error Code 6: Overflow

  1. #1
    Shawn
    Guest

    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

  2. #2
    Nigel
    Guest

    Re: Error Code 6: Overflow

    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" <Shawn@discussions.microsoft.com> wrote in message
    news:33AEBE3F-A1D6-4167-A5A6-DE96945EED1C@microsoft.com...
    > 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




  3. #3
    keepITcool
    Guest

    Re: Error Code 6: Overflow

    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.


  4. #4
    Shawn
    Guest

    Re: Error Code 6: Overflow

    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" <Shawn@discussions.microsoft.com> wrote in message
    > news:33AEBE3F-A1D6-4167-A5A6-DE96945EED1C@microsoft.com...
    > > 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

    >
    >
    >


  5. #5
    Shawn
    Guest

    Re: Error Code 6: Overflow

    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.

    >


  6. #6
    Tom Ogilvy
    Guest

    Re: Error Code 6: Overflow

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

    --
    Regards,
    Tom Ogilvy


    "Shawn" <Shawn@discussions.microsoft.com> wrote in message
    news:DE49B23F-A956-48CA-A906-0315BC6488AD@microsoft.com...
    > 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" <Shawn@discussions.microsoft.com> wrote in message
    > > news:33AEBE3F-A1D6-4167-A5A6-DE96945EED1C@microsoft.com...
    > > > 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

    > >
    > >
    > >




  7. #7
    Nigel
    Guest

    Re: Error Code 6: Overflow

    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" <xrrcvgpbby@puryyb.ay> wrote in message
    news:xn0e51r9vcryw5h006keepitcoolnl@news.microsoft.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.




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1