Overflow and variable issues. Please help me print to a table.

[pawest]

The overflow issues has been posted about before but I can't solve it as it relates to my code. Could someone please help me print data to a table without any overflow/variable issues?

Sub PrintDataTable()

'General variables
Dim symCell
Dim rngCell

Dim dateVal
Dim volVal
Dim symbol As String
Dim lastDataRow As Integer
Dim rng As Range

Sheets("BB Prod Data").Activate

For Each symCell In Sheets("Prod Data").Range("A1:DZU1")
    
    Sheets("Prod Data").Activate
    
    If symCell <> "" Then

        symbol = symCell.Text
        
        With Sheets("Prod Data")
            Set rng = .Range(.Cells(symCell.Offset(3, 0).Row, symCell.Column), .Cells(symCell.End(xlDown).Row, symCell.Column))
        End With
                
        For Each rngCell In rng
            
            dateVal = rngCell.Value
            volVal = rngCell.Offset(0, 1).Value
            
            Sheets("DataTable").Activate
                    
            lastDataRow = Sheets("DataTable").Range("B1040000").End(xlUp).Row
                    
            Sheets("DataTable").Range("B" & lastDataRow + 1).Value = symbol
            Sheets("DataTable").Range("C" & lastDataRow + 1).Value = dateVal
            Sheets("DataTable").Range("D" & lastDataRow + 1).Value = volVal
            
            'Sheets("Prod Data").Activate
 
        Next rngCell
        
    End If

Next symCell

Debug.Print "DONE"

End Sub

 

[Rick Rothstein]

Dim lastDataRow As Integer

Do you have more than 32768 rows of data? If so, the above declaration is guaranteed to overflow. Try Dim'ming that variable as Long and see if that stops the error. For your future programming efforts, on today's modern computers, there is no reason to use Integer data type as there is no savings in doing so... I would recommend always using Long instead (and Double instead of Single as well)... there are exceptions to this rule, but I doubt you will ever run into them (Windows API function calls and other's functions using ByRef arguments typed as Integer are two that come to mind).

 

[Norie]

The first thing you need to do is change Integer to Long when declaring lastDataRow.

Dim lastDataRow As Long

Do that and try the code again.

 

[pawest]

What a rookie mistake. This worked.

I haven't been programming in VBA lately. I've been doing some work with Perl. I come back to VBA and know nothing!

Thanks Rick and Norie!