Overflow error, while counting for values in the column and pasting in another sheet

Question

I am having two sheets , sht1 and sht2. I am trying to count value in the column R, S, T, U if it contains 1 and paste them in a table in sheet2.

First, I always, look for the calendar week in sht2, it checks for the present week number, and then I check the cw printed in my sheet1, if they are equal then it count for the number of 1 in the column I have mentioned.

I am getting an overflow error in the below line

For j = 5 To Sheets("sht1").Cells(Rows.Count, 23).End(xlUp).Row

Sub result()
Dim i As Integer
Dim j As Integer
Dim cnt As Integer
Dim cntU, cntS, CntV As Integer
Dim Sht As Worksheet
Dim totalrows As Long
Set Sht = Sheets("sht2")
Sheets("sht1").Select
totalrows = Range("A5").End(xlDown).Row
n = Worksheets("sht1").Range("A5:A" & totalrows).Cells.SpecialCells(xlCellTypeConstants).Count
For i = 2 To WorksheetFunction.Count(Sht.Columns(1))
cntT = 0
cntU = 0
cntS = 0
CntV = 0
If Sht.Range("A" & i) = Val(Format(Now, "WW")) Then Exit For
Next i
 For j = 5 To Sheets("sht1").Cells(Rows.Count, 23).End(xlUp).Row
 If Sht.Range("A" & i) = Range("W" & j) And Range("R" & j) = "1" Then cntT = cntT + 1
 If Sht.Range("A" & i) = Range("W" & j) And Range("S" & j) = "1" Then cntU = cntU + 1
 If Sht.Range("A" & i) = Range("W" & j) And Range("T" & j) = "1" Then cntS = cntS + 1
 If Sht.Range("A" & i) = Range("W" & j) And Range("U" & j) = "1" Then CntV = CntV + 1
If cntU <> 0 Then Sht.Range("D" & i) = cntU
If cntS <> 0 Then Sht.Range("E" & i) = cntS
If cntT <> 0 Then Sht.Range("C" & i) = cntT
If n <> 0 Then Sht.Range("B" & i) = n
If CntV <> 0 Then Sht.Range("F" & i) = CntV
Next j
If cntT + cntU + cntS + CntV <> 0 Then
Sht.Range("G" & i) = CntV / n
Sht.Range("H" & i) = cntS / n
Sht.Range("I" & i) = cntU / n
Sht.Range("J" & i) = cntT / n
End If
End Sub

anylead would be helpful.

simply change your Integer to Long... hint: Dim cntU, cntS, CntV As Integer only CntV is Integer! cntU and cntS are Variant ;) — Dirk Reichel
also read THIS. (Long is faster and uses less memory in VBA then Integer does) — Dirk Reichel
Unrelated to your error, but potential source of a future error, you should also change Rows.Count to Sheets("sht1").Rows.Count. — YowE3K
@jsotola - not in a single workbook, no, but it can happen between workbooks, so it's a good habit to always qualify Rows.Count with a sheet reference. — Tim Williams
Are you still getting the error after changing the declaration of j to be Long instead of Integer? And it is definitely an overflow error on the For j = 5 To Sheets("sht1").Cells(Rows.Count, 23).End(xlUp).Row line? — YowE3K

curious curious · Accepted Answer · 2017-08-07T01:46:44

I am not very sure of line n = Worksheets("sht1").Range("A5:A" & totalrows).Cells.SpecialCells(xlCellTypeConstants).Count indicated in the question.

Apart from this, there seems to be a few more issues with your code block. To figure out those, best is take a look at the below updated code which counts 1 separately in the columns R, S, T, U for the current week, pastes the result under the corresponding week in sht2, and calculates their share in the total pie.

Let me know if this is what you are looking for.

Sub result()
    Dim i As Long, j As Long, cntR As Long, cntS As Long, cntT As Long, cntU As Long, Sht As Worksheet
    Set Sht = Sheets("sht2")
    Sheets("sht1").Select
    For i = 2 To WorksheetFunction.CountA(Sht.Columns(1))
        If Sht.Range("A" & i) = Val(Format(Now, "ww")) Then Exit For
    Next i
    Sht.Range("C" & i & ":" & "J" & i).ClearContents
    For j = 5 To WorksheetFunction.CountA(Columns("W"))
        If Sht.Range("A" & i) = Range("W" & j) Then
            If Range("R" & j) = 1 Then cntR = cntR + 1
            If Range("S" & j) = 1 Then cntS = cntS + 1
            If Range("T" & j) = 1 Then cntT = cntT + 1
            If Range("U" & j) = 1 Then cntU = cntU + 1
        End If
    Next j
    If cntR <> 0 Then Sht.Range("C" & i) = cntR
    If cntS <> 0 Then Sht.Range("D" & i) = cntS
    If cntT <> 0 Then Sht.Range("E" & i) = cntT
    If cntU <> 0 Then Sht.Range("F" & i) = cntU
    If cntR + cntS + cntT + cntU <> 0 Then
        Sht.Range("G" & i) = cntR / (cntR + cntS + cntT + cntU)
        Sht.Range("H" & i) = cntS / (cntR + cntS + cntT + cntU)
        Sht.Range("I" & i) = cntT / (cntR + cntS + cntT + cntU)
        Sht.Range("J" & i) = cntU / (cntR + cntS + cntT + cntU)
    End If
    Sht.Range("G" & i & ":J" & i).NumberFormat = "0%"
End Sub

Overflow error, while counting for values in the column and pasting in another sheet

1 Answers