Solved

VBA - runtime error 6 - Overflow

Posted on 2010-11-25
3
1,362 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
  • 2
3 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

815 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now