object variable not set error

jishnu

New Member
Joined
Mar 25, 2019
Messages
15
Rich (BB code):
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
 
Last edited by a moderator:

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
What should nextRow be?
A cell, a value, a row, a number?
 
Upvote 0
In that case try
Code:
If Not findRng Is Nothing Then Call SearchForPKZ(findRng.Row)
 
Upvote 0
Code:
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.
 
Last edited:
Upvote 0
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.
Rich (BB code):
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 :(
 
Last edited by a moderator:
Upvote 0
What is the error message?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top