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

 

[jishnu]

SearchForPKZ also uses find function to find a particular value in the same sheet

 

[RoryA]

Then you can't use FindNext here, as I said, you have to use Find again with the same parameters.

 

[jishnu]

i got it.. let me try that.
thanks a ton RoryA

 

[jishnu]

Thanks a lot for the answers. It helped me so so much. I was able to complete my succesfully. Thanks a lott!!

 

[Fluff]

Glad you were able to sort it out & thanks for the feedback

 

[jishnu]

I have one more doubt. I would be so grateful if anyone can help me.
I am searching for a string in more than 1 worksheet (in a entire workbook with more than 10 sheets) . For this i am looping through all the worksheets to find the results. Is there any way for me to know , IN HOW MANY SHEETS THE VALUE HAS BEEN FOUND?? Precisely, i want to know whether a value is present in 2 sheets.

the code is given below.

Sub LoopThroughSheets(i)
 Dim findRng As Range
 Dim notFound As Boolean
 
 notFound = True
 
 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
        Column = findRng.Column
        notFound = False
        Set findRng = .UsedRange.Find(MKB_array(j), After:=Cells(firstRow, Column), lookat:=xlPart)
        If Not findRng Is Nothing Then
           CurrentRow = findRng.row
           Call SearchForPKZ(CurrentRow, i)
        End If
       
        Do
       
        Set findRng = .UsedRange.Find(MKB_array(j), After:=Cells(CurrentRow, Column), lookat:=xlPart)
         
        If Not findRng Is Nothing Then
           CurrentRow = findRng.row
           Call SearchForPKZ(CurrentRow, i)
        End If


        Loop While CurrentRow <> firstRow     ' here it goes for first row again at the end of the loop. so it better not to call searchforPKZ wt the first time when we find MKB
      
   End If


      End With


     Next ws
   
   Next j
   
  If notFound Then
  Workbooks(Plannungsdatei).Worksheets("DB").Cells(i, ColOfSYSTT_inPlannung).Interior.ColorIndex = 3 ' red
  End If


End Sub

Thanks a lot again for helping me out so far.