I am trying to find the multiple row numbers in which a particular value is found inside.
In the code shown below, the red coloured line shows an error
Code
[/COLOR][/COLOR] [COLOR=#333333][COLOR=#333333]Sub LoopThroughSheets()[/COLOR][/COLOR] [COLOR=#333333][COLOR=#333333]Dim findRng As Range[/COLOR][/COLOR] [COLOR=#333333][COLOR=#333333]Dim firstRow As Long[/COLOR][/COLOR] [COLOR=#333333][COLOR=#333333]Dim row As Long[/COLOR][/COLOR] [COLOR=#333333][COLOR=#333333]Dim allFinds As String[/COLOR][/COLOR] [COLOR=#333333][COLOR=#333333]Dim numberOfRowsWithSameMKB As Integer[/COLOR][/COLOR] [COLOR=#333333][COLOR=#333333]Workbooks(SYSDatei).Activate[/COLOR][/COLOR] [COLOR=#333333][COLOR=#333333]For j = 0 To SizeOfMKB_array - 1[/COLOR][/COLOR] [COLOR=#333333][COLOR=#333333]For Each ws In Workbooks(SYSDatei).Worksheets[/COLOR][/COLOR] [COLOR=#333333][COLOR=#333333]wsName = ws.Name[/COLOR][/COLOR] [COLOR=#333333][COLOR=#333333]With Workbooks(SYSDatei).Worksheets(wsName)[/COLOR][/COLOR] [COLOR=#333333][COLOR=#333333]Set findRng = .UsedRange.Find(MKB_array(j), lookat:=xlPart)[/COLOR][/COLOR] [COLOR=#333333][COLOR=#333333]If Not findRng Is Nothing Then[/COLOR][/COLOR] [COLOR=#333333][COLOR=#333333]firstRow = findRng.row[/COLOR][/COLOR] [COLOR=#333333][COLOR=#333333]MsgBox firstRow[/COLOR][/COLOR] [COLOR=#333333][COLOR=#333333]Call SearchForPKZ(firstRow)[/COLOR][/COLOR] [COLOR=#333333][COLOR=#333333]Do[/COLOR][/COLOR] [COLOR=#333333][COLOR=#333333]Set findRng = .UsedRange.FindNext(findRng)[/COLOR][/COLOR] [COLOR=#333333][COLOR=#333333]If Not findRng Is Nothing Then Call SearchForPKZ(findRng.row)[/COLOR][/COLOR] [COLOR=#ff0000]Loop While Not findRng Is Nothing And findRng.row <> firstRow[/COLOR] [COLOR=#333333][COLOR=#333333]rowArray = Split(allFinds, ",")[/COLOR][/COLOR] [COLOR=#333333][COLOR=#333333]numberOfRowsWithSameMKB = UBound(rowArray)[/COLOR][/COLOR] [COLOR=#333333][COLOR=#333333]End If[/COLOR][/COLOR] [COLOR=#333333][COLOR=#333333]End With
[/COLOR][/COLOR] and if i remove ' And findRng.row <> firstRow ' , it works but loops infinitely which is not what i wanted So it means only this portion has an issue.
Can anyone help me please