Overflow

[TP986]

Hi,

I just wrote a bit of VBA to check 6 columns of data and determine over 6 x 30 rows, the percentage of "Yes" versus the overall data. The first time i tried it (well...the first time it worked ) I ran through it line by line using F8. Worked like a charm, and 6 x 6 cells on the 4th sheet were filled with the data I'm looking for. I then put the code under a command button to run it from the sheet, and got the "Run-time error '6': Overflow" error. Can anyone tell me what happened here and help me out on solving it? Thanks!


Code:

Private Sub Trend()
Dim sh As Worksheet
Dim rn As Range
Set sh = ThisWorkbook.Sheets(1)
Dim xRange As Range
Dim Kolom, Cat, ScRegel, BBblok, Bblok, Eblok, Tel, Typ, y, n, s, t As Long

Set rn = sh.UsedRange
Bblok = rn.Rows.Count + rn.Row - 1

BBblok = Bblok
Kolom = 7
Cat = 1
Typ = 1




For Typ = 1 To 6


Bblok = BBblok
ScRegel = 7




For Tel = 1 To 6

Eblok = Bblok
Bblok = Bblok - 30

Set xRange = Range(Cells(Bblok, Kolom), Cells(Eblok, Kolom))
y = Application.WorksheetFunction.CountIf(xRange, "Yes")
n = Application.WorksheetFunction.CountIf(xRange, "No")
t = y + n
s = y / t

Sheets(4).Activate
Cells(ScRegel, Cat).value = s

Bblok = Bblok - 30
ScRegel = ScRegel - 1
Sheets(1).Activate

Next Tel


Cat = Cat + 1
Kolom = Kolom + 1


Next Typ


End Sub

 

[MARK858]

Do you get the same error if you dim them correctly i.e.

This

Dim Kolom, Cat, ScRegel, BBblok, Bblok, Eblok, Tel, Typ, y, n, s, t As Long

probably (I only say probably as I haven't checked the full code to make sure they all should be Longs) should be

Dim Kolom As Long, Cat As Long, ScRegel As Long, BBblok As Long, Bblok As Long
Dim Eblok As Long, Tel As Long, Typ As Long, y As Long, n As Long, s As Long, t As Long

in Excel VBA

 

[TP986]

The dim wasn't the problem, it actually works the way it's stated. Just found out I had the button with code set on one sheet, and the data on another. Moving them to the same sheet solved the overflow problem; values were 0 so that was the cause for the error. Just some tweaking should finish the job. Thanks for the reply

 

[MARK858]

it actually works the way it's stated

It works because it is treating Kolom, Cat, ScRegel, BBblok, Bblok, Eblok, Tel, Typ, y, n, s as variants which is the most inefficient type.

In VBA you must explicitly state each variables type or there is no point declaring them at all as VBA defaults to the variant type.

I had the button with code set on one sheet, and the data on another

and that is why it is good practice to explicitly state the sheets when coding