Code to select items from list based on criteria - fails on first item then subsequently works

[dccarm]

Can anyone help? The code below is designed to extract priority 1 job numbers from a list of tasks and then pass these back to another sheet. (Every task on the sheet is allocated a priority from 1 to 5.)

Frustratingly though, it fails to extract anything the first time it finds a priority 1 task, but works perfectly on all subsequent rows. Why would this be?

When I insert MsgBox commands through the code to check the FindAddress, PrintJob and PrintPlot values, it correctly identifies the first FindAddress value, but returns a blank for the other two.

I've marked the section of code where it falls down in blue.

Sub PrintSheets()    Dim R As Range, FindAddress As String
    Dim PrintJob As String
    Dim PrintPlot As String
    Dim LngLastRow As Long
    
If MsgBox("This process will print job sheets for all priority 1 tasks. Please press OK if you wish to continue.", vbOKCancel) = vbCancel Then Exit Sub


Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False


Application.Dialogs(xlDialogPrinterSetup).Show


Worksheets("Tasks").Visible = True
Worksheets("Job Sheet Print Ex").Visible = True


     
     'Set the range in which we want to search in
[COLOR=#0000ff]    With Range("TaskPriority")[/COLOR]
[COLOR=#0000ff]         'Search for the first occurrence of the item[/COLOR]
[COLOR=#0000ff]        Set R = .Find("1")[/COLOR]
[COLOR=#0000ff]         'If a match is found then[/COLOR]
[COLOR=#0000ff]        If Not R Is Nothing Then[/COLOR]
[COLOR=#0000ff]             'Store the address of the cell where the first match is found in a variable[/COLOR]
[COLOR=#0000ff]            FindAddress = R.Address[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]            Do[/COLOR]


[COLOR=#0000ff]                    PrintJob = Cells(R.Row, R.Column - 8).Value[/COLOR]
[COLOR=#0000ff]                    PrintPlot = Cells(R.Row, R.Column - 7).Value[/COLOR]


[COLOR=#0000ff]            With ThisWorkbook.Worksheets("Job Sheet Print Ex")[/COLOR]
[COLOR=#0000ff]                    .Range("JobPrintPick").Value = PrintJob[/COLOR]
[COLOR=#0000ff]                    .Range("PlotPrintPick").Value = PrintPlot[/COLOR]
[COLOR=#0000ff]            End With[/COLOR]
            
    Sheets("Job Sheet Print Ex").Select


    Range("TaskTable").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
    Range("CutPrintFilter"), CopyToRange:=Range("CutPrintFilterTo"), Unique:=False


    Range("TaskTable").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
    Range("AssPrintFilter"), CopyToRange:=Range("AssPrintFilterTo"), Unique:=False


    
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("Tasks").Select
                    
                 'Search for the next cell with a matching value
                Set R = .FindNext(R)
                 'Search for all the other occurrences of the item i.e.
                 'Loop as long matches are found, and the address of the cell where a match is found,
                 'is different from the address of the cell where the first match is found (FindAddress)
            Loop While Not R Is Nothing And R.Address <> FindAddress
        End If
    End With
     
     'Clear memory
    Set R = Nothing
     


Worksheets("Tasks").Visible = False
Worksheets("Job Sheet Print Ex").Visible = False


Application.DisplayAlerts = True
Application.ScreenUpdating = True


Sheets("Menu").Select


End Sub

 

[dccarm]

Anyone?