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:

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,650
Office Version
  1. 365
Platform
  1. Windows
What should nextRow be?
A cell, a value, a row, a number?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,650
Office Version
  1. 365
Platform
  1. Windows
In that case try
Code:
If Not findRng Is Nothing Then Call SearchForPKZ(findRng.Row)
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,450
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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:

jishnu

New Member
Joined
Mar 25, 2019
Messages
15
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,650
Office Version
  1. 365
Platform
  1. Windows
What is the error message?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,523
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,522
Messages
5,529,329
Members
409,863
Latest member
stacy09
Top