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
– Dirk Reichel, Commented Aug 6, 2017 at 15:43
also read THIS. (Long is faster and uses less memory in VBA then Integer does) — Dirk Reichel
– Dirk Reichel, Commented Aug 6, 2017 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
– YowE3K, Commented Aug 6, 2017 at 16:24
@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
– Tim Williams, Commented Aug 6, 2017 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
– YowE3K, Commented Aug 6, 2017 at 20:56

curious · Accepted Answer · 2017-08-07 01:46:44Z

1

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

answered Aug 7, 2017 at 1:46

curious

1,4925 gold badges18 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Mikz Over a year ago

Sure I will have a look and let you know if this is working

Mikz Over a year ago

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

YowE3K Over a year ago

@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.

curious Over a year ago

also, if you can edit your question and replace it with the updated code

Mikz Over a year ago

@YowE3K the value of the line Sheets("sht1").Cells(Sheets("sht1").Rows.Count, 23).End(xlUp).Row is "10486"

|

Collectives™ on Stack Overflow

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

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related