Solved

VBA - runtime error 6 - Overflow

Posted on 2010-11-25
3
1,414 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 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

628 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