object variable not set error

[jishnu]

Sub LoopThroughSheets()
 Dim findRng As Range
 Dim firstRow As Long
 Dim nextRow As Variant
 Dim allFinds As String
 Dim numberOfRowsWithSameMKB As Integer
 
 Workbooks(SYSDatei).Activate


  For j = 0 To SizeOfMKB_array - 1
    For Each ws In Workbooks(SYSDatei).Worksheets
      wsName = ws.Name
      With Workbooks(SYSDatei).Worksheets(wsName)


      Set findRng = .UsedRange.Find(MKB_array(j), lookat:=xlPart)


      If Not findRng Is Nothing Then
            firstRow = findRng.row
            'MsgBox firstRow
            Call SearchForPKZ(firstRow)
          
            Do
            
                                                                                             
            Set findRng = .UsedRange.FindNext(findRng)


            nextRow = findRng.row
           
            Call SearchForPKZ(nextRow)
           
      
      End If
      End With




     Next ws
   Next j


End Sub

vba editor is showing error message on the red coloured text above.. pls helppp

 

[Fluff]

What should nextRow be?
A cell, a value, a row, a number?

 

[jishnu]

it is a row number

 

[Fluff]

In that case try

If Not findRng Is Nothing Then Call SearchForPKZ(findRng.Row)

 

[JLGWhiz]

Set findRng = .UsedRange.FindNext(findRng)


[COLOR=#ff0000]nextRow = findRng.row[/COLOR]

It appears to be telling you that it did not find the findRng variable value in the FindNext statement and that made the findRng variable lose its value as an object variable. Or that when the SearchForPKZ macro was called the findRng variable was set to nothing and it would not find the next occurence of the original value.

 

[jishnu]

Thanks a lot Fluff..That was perfect !!

But now it came with another issue. Sorry for bothering much but as i am pretty new to vba iam not able to figure out the issue.
In the code shown below, the red coloured line shows an error and if i remove ' And findRng.row <> firstRow ' , it works but loops infinitely which means only this portion has an issue.

Sub LoopThroughSheets()
 Dim findRng As Range
 Dim firstRow As Long
 Dim row As Long
 Dim allFinds As String
 Dim numberOfRowsWithSameMKB As Integer
 
 Workbooks(SYSDatei).Activate


  For j = 0 To SizeOfMKB_array - 1
    For Each ws In Workbooks(SYSDatei).Worksheets
      wsName = ws.Name
      With Workbooks(SYSDatei).Worksheets(wsName)


      Set findRng = .UsedRange.Find(MKB_array(j), lookat:=xlPart)


      If Not findRng Is Nothing Then
            firstRow = findRng.row
            MsgBox firstRow
            Call SearchForPKZ(firstRow)
          
            Do
            
                                                                                              
            Set findRng = .UsedRange.FindNext(findRng)
            If Not findRng Is Nothing Then Call SearchForPKZ(findRng.row)
            
            Loop While Not findRng Is Nothing And findRng.row <> firstRow
            
                                                                                               
            rowArray = Split(allFinds, ",")
            
            numberOfRowsWithSameMKB = UBound(rowArray)
               
                                                                                                
      
      End If
      End With

Please help

 

[jishnu]

Thank you JLGWhiz ..

 

[Fluff]

What is the error message?

 

[jishnu]

"object variable not set " for findRng.row

 

[RoryA]

What does SearchForPKZ actually do? If it uses FInd, your FindNext won't work here and you'll have to use Find again with the same values as previously.