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, Aug 06 '17 at 15:43
also read [THIS](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long). (`Long` is faster and uses less memory in VBA then `Integer` does) — Dirk Reichel, Aug 06 '17 at 15:48
Unrelated to your error, but potential source of a future error, you should also change `Rows.Count` to `Sheets("sht1").Rows.Count`. — YowE3K, Aug 06 '17 at 16:24
@YowE3K, now that i'm reading your comment, is it possible to have multiple differing Rows.Count values in a workbook — jsotola, Aug 06 '17 at 16:54
@DirkReichel with your suggestion i am not able to get the complete result. just the total values are printing. the rest count values are not getting printed — Mikz, Aug 06 '17 at 17:08
@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, Aug 06 '17 at 19:32
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, Aug 06 '17 at 20:56
@YowE3K Ya, I am still getting the error. Ya while debugging. I am getting error in that particular line — Mikz, Aug 07 '17 at 02:23
When it crashes, please look to see what the value of `Sheets("sht1").Cells(Sheets("sht1").Rows.Count, 23).End(xlUp).Row` is and let us know. (I can't see how it can exceed the value of a `Long` - Excel only handles 1048576 rows which can be easily handled as a `Long`.) — YowE3K, Aug 07 '17 at 03:32
@Mikz `For j = 5 To 10486` should not give an overflow error. Are you 100% sure that that is the error you are getting and that that is the line on which the error is occurring? — YowE3K, Aug 08 '17 at 08:14
YowE3K , I am sure i am getting the Overflow error in that Line. I debugged it twice and checked it — Mikz, Aug 08 '17 at 08:17
Can you tell me , how i can check the populated cell, i have my data only upto 500 rows, but this Shows 10486 . — Mikz, Aug 08 '17 at 08:18
Go to the last row of your spreadsheet (probably row 1048576). Then go to column W. Then press End. Then press the up arrow. What cell does it place you at? (I assume it will be W10486.) What is in that cell? — YowE3K, Aug 08 '17 at 08:24

score 1 · Accepted Answer · answered Aug 07 '17 at 01:46

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

this works fine with me , but cn you tell why i have the error particularly with one worksheet ?, because i have the same kind of code implemented in many worksheet. and i works fine without any error. just with this worksheet i am getting the overflow error — Mikz, Aug 07 '17 at 22:52
@Mikz If you tell us what the value of `Sheets("sht1").Cells(Sheets("sht1").Rows.Count, 23).End(xlUp).Row` is when your code crashes, we might be able to tell you why you are getting the overflow error. — YowE3K, Aug 07 '17 at 22:59
also, if you can edit your question and replace it with the updated code — curious, Aug 08 '17 at 04:18
@YowE3K the value of the line Sheets("sht1").Cells(Sheets("sht1").Rows.Count, 23).End(xlUp).Row is "10486" — Mikz, Aug 08 '17 at 07:28
@curious you have removed the value of "n" from my code, How do i add it in my code now? — Mikz, Aug 08 '17 at 07:29
by this time u must have replaced all integers with long. so whatever changes u have made, just update your latest codes in the question n i'll take a look — curious, Aug 08 '17 at 11:16

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

1 Answers1