error while using FindNext function

  • 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 [Blocked Image: https://www.mrexcel.com/forum/images/smilies/icon_laugh.gif] So it means only this portion has an issue.


    Can anyone help me please

  • MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

    *

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!