VBA don't see objects i create outside the loop

Question

So i got problem with FindNext in my code, its like vba don't see objects i created outside the loop.

Sub Macro1()

'Dwa skoroszyty
Dim ws1, ws2 As Worksheet
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Wynik")

Dim NaglowekWiersz, NaglowekKolumna As Integer
NaglowekKolumna = 1
NaglowekWiersz = 1
Worksheets("Sheet1").Activate

Set LpDoZnalezienia = ws1.Range("A:A").Find("lp.", LookAt:=xlPart)
Set RazemDoZnalezienia = ws1.Range("A:A").Find("Razem", LookAt:=xlWhole)

'Petla do szukania pracowników
For a = 1 To 3

If (a > 1) Then
    Set LpDoZnalezienia = ws1.Range("A:A").FindNext(LpDoZnalezienia).Offset(1, 0)
    Set RazemDoZnalezienia = ws1.Range("A:A").FindNext(RazemDoZnalezienia)
End If

'Znalezienie danego pracownika
Set Pracownik = ws1.Range(LpDoZnalezienia, RazemDoZnalezienia).Resize(, 12)
Worksheets("Wynik").Activate

For b = 1 To 35
'Dana Wartość z arkusza wynik aka nagłówek
Dim szukanaWartosc As String
szukanaWartosc = ws2.Cells(1, NaglowekKolumna).Value

'Znaleziona wartosc w arkuszu Sheet1
Worksheets("Sheet1").Activate
Set WartoscDoZnalezienia = Pracownik.Find(szukanaWartosc, LookAt:=xlWhole)
If WartoscDoZnalezienia Is Nothing Then
    GoTo Line
Else
    Set Znalezione = WartoscDoZnalezienia.Offset(0, 2)
End If
Worksheets("Wynik").Activate
ws2.Cells(NaglowekWiersz + 1, NaglowekKolumna) = Znalezione.Value

Line:
'Przesuniecie naglowka o 1 w arkuszu wynik aby szukalo kolejnej wartości
NaglowekKolumna = NaglowekKolumna + 1
Next b
NaglowekKolumna = a
NaglowekWiersz = NaglowekWiersz + 1
Next a

End Sub

When i try to use FindNext with earlier declared Find its giving me Run-time error 91 that Object or variable is not set in this line:

Error when the code go to findNext

It works perfectly when I copy those 2 line with find next outside the loop. Can somebody tell me what im doing wrong in my code?

What is a when the bug happens? From your description, I would guess that it occurs when a = 3. You should check if LpDoZnalezienia is Nothing before using it. Also, using Option Explicit and declaring all of your variables would make your code more robust. — John Coleman
By a im iterating every employer, the problem is not in the loop, because when i seting loop to do once its working fine, i have problem when it come to 2nd iteration beacuse its like vba don't see that i deaclared find outside of loop, so its crashing when it come to FindNext. — user7909124
It hardly matters what you declared outside the loop, because inside the loop you are overwriting the LpDoZnalezienia with a new value. Error 91 is caused by .Offset(1, 0) when FindNext returns Nothing. — GSerg

Adam Vincent Adam Vincent · Accepted Answer · 2017-09-04T12:37:12

Find() and FindNext() are a little tricky and the documentation is a little confusing.

The .Find() used for .FindNext is stored in ws1.Range("A:A"), and not in LpDoZnalezienia. In a nutshell, you need to keep the instance of the Range that the find is being operated on, in scope.

So the solution to your problem will be:

With ws1.Range("A:A")
    Set LpDoZnalezienia = .Find("lp.", LookAt:=xlPart)
    If Not LpDoZnalezienia Is Nothing Then
        For a = 1 To 3
        If (a > 1) Then
            Set LpDoZnalezienia = .FindNext(LpDoZnalezienia).Offset(1, 0)
        End If
    End If
End With

With ws1.Range("A:A")
    Set RazemDoZnalezienia = .Find("Razem", LookAt:=xlWhole)
    If Not RazemDoZnalezienia Is Nothing Then
        For a = 1 To 3
        If (a > 1) Then
            Set RazemDoZnalezienia = .FindNext(RazemDoZnalezienia)
        End If
    End If
End With

Of note: If you only have 1 or 2 occurrences of "lp." or "Razem" you will get the first Range second time, there is no internal handling to check if .FindNext() has already given you a specific Range. You will have to code this yourself..example:

With ws.Cells
    Set fr = .Find("value", , xlValues, xlWhole)
    If Not fr Is Nothing Then
        frAddress = fr.Address
        Do
            Set fr = .FindNext(After:=fr)
            'Do something with found range here.
             Debug.print fr.Address
        Loop While frAddress <> fr.Address
    End If
End With

VBA don't see objects i create outside the loop

Error when the code go to findNext

1 Answers