Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBA - runtime error 6 - Overflow

Posted on 2010-11-25
3
Medium Priority
?
1,452 Views
Last Modified: 2012-05-10
Hi,

I have created a report that should print out all of the outstanding balances for all clients.  Now, my query result produces 1080 rows, and I would like those rows displayed on my sheet, but I get the following error:  Runtime Error 6 - Overflow.

See code attached:

Can anyone help?
Private Sub ExtractData(options As CitizenBalanceOpt)
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sql As String
    Dim ToDate As Date
    Dim FromDate As Date
    Dim AsAtDate As Date
    Dim rng As Range
    Dim i As Integer, j As Integer
    Dim lTotal As Boolean
    Dim firstRow As Boolean
    Dim cStatus As String
    Dim valueCheck As Integer
    Dim totMonths As Integer
    Dim TotalVal As Integer
    Dim RemainMonths As Integer
    Dim TotPaid As Integer
    Dim ValRemaining As Integer
    
    application.StatusBar = "Connecting to Server..."
    
    Set cn = ozConnection()
    Set rs = New ADODB.Recordset

    Range("CitizenBalanceReport!CBReportOptions").value = "As At: " & Format(options.asAt, "dd MMM yyyy")
    Range("CitizenBalanceReport!CBAsAt").value = options.asAt
    
    Call ActiveSheet.Cells.Replace("<Date>", options.drawDate, xlPart, , False)

    application.StatusBar = "Connected. Retrieving records..."
    AsAtDate = options.asAt
    
        sql = _
            "SELECT Con.ContractID, Con.CustomerID, Con.FromDate, Con.ToDateContracted, " & _
            "Con.AmountExcl, C.CustomerRef, C.Name " & _
            "FROM Contracts Con, Customers C " & _
            "WHERE Con.CustomerID = C.CustomerID AND Con.ToDateContracted >= " & ToSql(AsAtDate) & _
            " AND Con.AmountExcl > 0 GROUP BY C.CustomerRef"
        rs.Open sql, cn
        
    Set rng = Range("CitizenBalanceReport!CBBody").Cells(1)
    i = 0: j = 0: valueCheck = 0
    cStatus = ""
    lTotal = False
    firstRow = True
    
    Do While Not rs.EOF
        If firstRow Then
         If Not IsNull(rs!ToDateContracted) Then
           ToDate = rs!ToDateContracted
           FromDate = rs!FromDate
             totMonths = DateDiff("m", FromDate, ToDate)
             RemainMonths = DateDiff("m", FromDate, AsAtDate)
             If RemainMonths > 0 Then
             If totMonths <> 0 Then
              TotalVal = rs!AmountExcl * totMonths
              TotPaid = rs!AmountExcl * RemainMonths
             End If
             End If
             ValRemaining = TotalVal - TotPaid
             rng.Offset(i, 0) = rs!CustomerRef
             rng.Offset(i, 1) = "" & rs!name
             End If
        End If
        
        rng.Offset(i, 4) = rng.Offset(i, 4) + ValRemaining
        
        If rng.Offset(i, valueCheck).value = 0 Then
            rng.Offset(i, valueCheck).value = ""
        End If
        
        rs.MoveNext
        
        If Not rs.EOF Then
            If Not (rs!CustomerRef = rng.Offset(i, 0).value) Then
                i = i + 1
               rng.Offset(i).EntireRow.insert (xlShiftDown)
               firstRow = True
            Else
                firstRow = False
            End If
        End If
    Loop
    
    application.StatusBar = ""

    ' Closes the table.
    rs.Close
    cn.Close ' to drop temp tables...
End Sub

Open in new window

0
Comment
Question by:NerishaB
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 34212996
I suspect TotalVal, TotPaid and ValRemaining should all be Double and not Integer. As a point of interest, you may as well declare all the other Integer variables as Long since that is what VBA uses behind the scenes anyway.
0
 

Author Closing Comment

by:NerishaB
ID: 34213014
Thanks. You're brilliant!!!
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34213040
I wish I were! I've just been doing this a while. :)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question