jeudi 15 juillet 2021

How do I make this code step over a column when a word is found in that column?

I have the below code that looks in a file location for a PDF with a matching work order number to what is column B, if it exists the code places the words "Ready to upload" into column S but I need it to check column R for the word "Review TSIL" and if it exists do not write anything in Column S and move on to next work order in Column B.

The first part works well, it will mark all work orders found with "Ready to upload" but wont skip over ones that have review tsil in column R. Please help

    Sub Readyupload()
    Dim fldr As FileDialog
    Dim sItem As String
    Dim c As Object
    Dim Path As String
    Dim FName As String
    Dim SPath As String
    Dim cell As Range
    
    
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        If .Show <> -1 Then GoTo nextcode
        sItem = .SelectedItems(1)
    End With
    
    With ActiveSheet
    Lrow = ActiveSheet.Cells(2000, 2).End(xlUp).Row
    
                For Each c In .Range("B2:B" & Lrow)
                Path = sItem
                FName = Dir(Path & "\*.PDF*")
                Do
                  If InStr(FName, c) > 0 And c <> "" Then
                    On Error Resume Next
                        If .Cells(c.Row, "R") = "Review TSIL" Then
                        On Error Resume Next
                            c.Offset(, 17) = "Ready to upload"
                            'On Error Resume Next
                        End If
                    End If
                FName = Dir()
            Loop Until FName = ""
        Next c
        End With
        MsgBox ("All TSIL's Marked ready for upload")
    nextcode: Exit Sub
    
    Set fldr = Nothing
    End Sub

Aucun commentaire:

Enregistrer un commentaire