Having issues with findnext function does not work like I want it.

fabdullaster · Feb 6th 2013

Can anyone look at the attached code and see what i am doing wrong. I tried first to search for PLCMSGString then when it finds it i tried to get the range from column A to where the string is found. Then try to search this new range for some other strings, but the first time if find the correct string it would go through the code, but when it reaches the .findnext it finds string other than PLCMSGString. the PLCMSGString is appears more than once, but all three will appear only once in the spreasheet. What i tried to accomplish is when i find PLCMSGString then search for the other strings in the same range if found, it is not found then go find the next PLCMSGString etc... If search the enire spreadsheet and did not find anything i put in comments in the excel cells.

Code

With Workbooks(PTPWBName).Sheets("Index ").Range(Cells(1, 1), Cells(PTPLastRow, PTPLastCol))
                                        Set Ind = .Find(PLCMSGString, LookIn:=xlValues, lookat:=xlWhole)
                                            If Not Ind Is Nothing Then
                                                IndAddress = Ind.Address
                                                Do
                                                    DistCount = 0
                                                    SourceCount = 0
                                                    IndRow = Range(Ind.Address).Row
                                                    IndCol = Range(Ind.Address).Column
                                                    Set Ind_1 = Workbooks(PTPWBName).Sheets("Index ").Range(Cells(IndRow, ColA), Cells(IndRow, IndCol)).Find(DistPLCName, LookIn:=xlValues, lookat:=xlWhole)
                                                    If Not Ind_1 Is Nothing Then
                                                       ' Ind1Address = Ind1.Address
                                                        Set Ind_2 = Workbooks(PTPWBName).Sheets("Index ").Range(Cells(IndRow, ColA), Cells(IndRow, IndCol)).Find(SourcePLCName, LookIn:=xlValues, lookat:=xlWhole)
                                                        If Not Ind_2 Is Nothing Then
                                                           ' Ind2Address = Ind2.Address
                                                            SourceCount = SourceCount + 1
                                                        End If
                                                    End If
                                                    
                                                    Set Ind = .FindNext(Ind)
                                                Loop While Not Ind Is Nothing And Ind.Address <> IndAddress
    
                                                
                                                If SourceCount < 1 Then
                                                    Set RngInd = Workbooks(NewWBName).Sheets(PLCTagSheets.Name).Cells(i, ColC)
                                                    RngInd.ClearComments
                                                    RngInd.AddComment "Missing Source/Dist PLC in PTP Index sheet"
                                                    RngInd.Comment.Visible = True
                                                End If
                                            Else
                                                    Set RngInd = Workbooks(NewWBName).Sheets(PLCTagSheets.Name).Cells(i, ColC)
                                                          RngInd.ClearComments
                                                          RngInd.AddComment "Missing Source/Dist PLC in PTP Index sheet"
                                                          RngInd.Comment.Visible = True
                                            End If
                                    End With

Display More

p45cal · Feb 6th 2013

Re: Having issues with findnext function does not work like I want it.

Without looking too closely at your code, what sticks out like a sore thumb as being not terribly robust is the .findnext line which, according to Help:"Continues a search that was begun with the Find method". That'd be the last find method executed. You have three instances of .Find before that, two of them are conditional. That means that any one of the three could have been the last .Find.
Solution, be explicit with what you're looking for again with .Find instead of .FindNext.
Replace that .findnext line with the likes of:
Set Ind = .Find(PLCMSGString, after:= Ind, LookIn:=xlValues, lookat:=xlWhole)

Having issues with findnext function does not work like I want it.

Participate now!

Share