Debugging Search code. excel2003

Fiske

Board Regular
Joined
Jun 15, 2015
Messages
82
Hi guys i need help debugging the code below.
Every time i run the debug, my data in the cell(1,2) will disappear and the code will jump from 2 if.execute()>0 to 3 and keep looping even though cell is empty which it should stop.
Please help me as i have stuck here for a few hours and no where near fixing it.
Thank you!

Code:
Sub rev()Dim pn As String, fldr As String, rev As String, fil As String
Dim i As Long, p As Integer, y As Integer


Do
For i = 0 To Rows.Count
Cells(i + 1, 2).Value = pn
GoTo 1
If Cells(i + 1, 2).Value = "" Then
Exit Sub


1:
          Application.ScreenUpdating = False
          With Application.FileDialog(msoFileDialogFolderPicker)
          fldr = "H:\E-Drawing Database PDF"
          End With


          With Application.ActiveCell
          Static x As Integer
          x = x + 1
          End With


          With Application.filesearch
               .NewSearch
               .LookIn = fldr
               .SearchSubFolders = True
               .Filename = pn
     
If pn = "" Then
Cells(i + 1, 3) = ""
On Error GoTo 3


2:  On Error GoTo 0
    If .Execute() > 0 Then
    For y = 1 To .FoundFiles.Count
    fil = .FoundFiles(y)
    If InStr(1, fil, "SUPERSEDED") = False Then
    If InStr(1, fil, "Superseded") = False Then
    p = p + 1
    If InStr(1, fil, "_") = 0 Then
    If Cells(i + 1, 3) = Empty Then
    Cells(i + 1, 3) = "##"
    End If
    Else
    If InStr(1, fil, "_") - InStr(1, fil, pn) = Len(pn) Then
    rev = Right(fil, Len(pn) - (InStr(1, fil, pn) - 1))
    
    rev = Left(rev, InStr(1, rev, ".") - 1)
    rev = Right(rev, Len(rev) - InStr(1, rev, "_"))
    
    If Not Cells(i + 1, 3) = "-" Then
    
    If rev > Cells(i + 1, 3) Then
    Cells(i + 1, 3) = rev
    End If
    ElseIf InStr(1, fil, "_") - InStr(1, fil, pn) = Len(Cells(i + 1, 2)) Then
    If Not InStr(1, fil, Cells(i + 1, 2)) = 0 Then
    rev = Right(fil, Len(fil) - (InStr(1, fil, pn) - 1))
    rev = Left(rev, InStr(1, rev, ".") - 1)
    rev = Right(rev, Len(rev) - InStr(1, rev, "_"))
    
    If Not Cells(i + 1, 3) = "##" Then
    If a > Cells(i + 1, 3) Then
    Cells(i + 1, 3) = rev
    Else
    Cells(i + 1, 3) = rev
    End If
    Else
    Cells(i + 1, 3) = rev
    End If
    End If
    End If
    End If
    End If
    Else: End If
   


3:
   Application.DisplayAlerts = False
   Application.DisplayAlerts = False
   GoTo 2
End If
Next y
End If
End If
End With
End If
Next
Loop Until Cells(i + 1, 2) = ""
MsgBox "End"
End Sub
 
Last edited:

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Fiske

Board Regular
Joined
Jun 15, 2015
Messages
82
PS. i forget to mention, the loop is loop the same process for the cell on the next row, same column until the cell on the next row is empty, then it will stop.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Try removing On Error Goto 3, then you might find out if there's an error causing code to skip to section 3.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,875
Members
414,106
Latest member
Tigretto

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
Top