Excel VBA: Overflow error

Question

Just started programming in VBA, I have a problem, and i don't know how to solve this. I think everything is ok. There shows Run-Time Error '6' Overflow when i want to run this macro.

Sub Działaj()
Dim Tablica(1 To 5000) As String
Dim Dni()
Dim kolumna As Integer
Dim wiersz As Integer
Dim licznik As Integer
Dim PF As Boolean
Dim tmp As Integer
Dim i As Integer
Dim tmp2 As String
licznik = 2
tmp = 0
PF = False
kolumna = 22
wiersz = 2
Do
    If Worksheets("Początkowe").Cells(wiersz, kolumna).Value <> vbNullString Then
        For i = 1 To licznik
            If Worksheets("Początkowe").Cells(wiersz, kolumna).Value = Tablica(i) Then 'debugger shows problem here i guess
                PF = True
                tmp = i
            End If
        Next i
    End If
    If Worksheets("Początkowe").Cells(wiersz, kolumna).Value = "koniec" Then
        Exit Do
    End If
    wiersz = wiersz + 1
Loop
End Sub

Can anyone tell me where i made a mistake? I would be very grateful.

One, you haven't set any value in Tablica(i). Second, if you don't find the value koniec, then the loop keep on until you grow out of your integer variable wiersz, around 32k. — nutsch
– nutsch, Commented Dec 2, 2013 at 22:27
What do you mean saying "you haven't set any value in Tablica(i)", can you give me an example? Don't worry about "koniec" it will be at the end of all data in all columns. — Domin1992
– Domin1992, Commented Dec 2, 2013 at 22:30
you're referencing If Worksheets("Początkowe").Cells(wiersz, kolumna).Value = Tablica(i), but at no point in your code prior to that line do you assign a value to the array, so Tablica(i) at that stage is an empty string. — nutsch
– nutsch, Commented Dec 2, 2013 at 22:36
Yes for now. I am going to fill that table data from worksheet. — Domin1992
– Domin1992, Commented Dec 2, 2013 at 22:38

nutsch · Accepted Answer · 2013-12-02 22:38:37Z

7

If you don't find the value koniec before row 32767, your variable wiersz will max out. If you want to continue past that, you should redefine it as Long.

You should also provide an exit for your loop, e.g. existing at the last used row. Instead of a do ... loop, I usually use the following code:

Dim lLastRow As Long, lWiersz As Long

lLastRow = Cells(Rows.Count, kolumna).End(xlUp).Row

For lWiersz= 1 To lLastRow


Next lWiersz

edited Dec 2, 2013 at 22:38

answered Dec 2, 2013 at 22:29

nutsch

5,9622 gold badges23 silver badges36 bronze badges

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

2 Comments

Domin1992 Over a year ago

Yap, thank you, it was right i forgot about "koniec" at the and of table. I cant set "if" to check if cell is empty because there are some cells empty int the middle of table. Thank you again.

nutsch Over a year ago

What I usually do in that case is a for...next, rather than a do...loop, see attached example

Mayur Pathak · Accepted Answer · 2020-09-07 06:14:51Z

0

you need to replace Dim object integer to long

like this

Dim wiersz As Integer -->>> Dim wiersz As long

Dim licznik As Integer -->>> Dim licznik As long

answered Sep 7, 2020 at 6:14

Mayur Pathak

946 bronze badges

Collectives™ on Stack Overflow

Excel VBA: Overflow error

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related