Searching selected range (offset) for values in external workbook range

[bifjamod]

I have a script that does an index/match function, loosely described as follows:
Gets the file number, which is selection.offset (0, -27), then searches an external workbook (DUTY.xlsx) to see if the file number is on there.
If it is, then it compares the amount associated with the file, found in selection.offset(0, -8) and the corresponding amount on Duty.xlsx to see if they match.
In the event that either the amounts do not match, OR the file is not found on the external workbook, that information is written to a text file.

All in all, this script works perfectly. However, there is a second part to the process, and that requires then checking each file number found on the external workbook and running it back through the selected range file numbers to see if it is there. If not, it will output another line to the aforementioned text file. If it is there, the routine just goes on to the next file.

My problem, and that which I asking for some suggestions on, as you Excel guru's might have a solution for me faster than I can dig one up through Google and experimentation, is this:

• How do I search the file numbers only in the selected range for the file numbers in the external workbook?
  

It's important that the search be limited to the file numbers in the selected range, because they will exist in the workbook, but just may not exist in the selected range (which, as mentioned and demonstrated in the script, is an offset value from the selected cells.)

My initial thought was to simply reverse the existing index/match function which works so beautifully, but the more I tried to write that line out, the more it seems to break down on me.

Any thoughts?

Background: the purpose of this script is to perform a double cross-check looking for missing files or discrepant amounts between the two reports. It first looks to the Manifest workbook, the primary data, and checks to see if the file and amount match what is in Quickbooks. However, it's also necessary to check the Quickbooks report to see if a file on it is not in the current Manifest selection (which corresponds to all the files associated with a monthly statement.) The end result is the text file listing all the discrepancies, which allows for quick reconciliation by reviewing the identified files.

Thank you in advance!

Sub ACH_PMS_Verify_QB()
    MsgBox "To verify Manifest data against Quickbooks data, " & _
        "First close down Excel; then run the monthly duty report " & _
        "from QB, saving as 'DUTY'. Then, select all cells containing " & _
        "the desired monthly statement number (column AB).", vbOKCancel
    
    Dim rCell As Range
    Dim aCell As Range
    Dim strFileNum As String
    Dim strDutyAmt As String
    Dim strQBDutyAmt As Variant
    Dim strQBFileNum As Variant
    Dim path As String
    
    Dim oQBVerify As TextStream
    Dim oFS As New Scripting.FileSystemObject
    path = "C:\Dropbox\Desktop Work File\ACH PMS QB Reconciliation Report " & Format(Date, "MMDDYY") & ".txt"
    Set oQBVerify = oFS.OpenTextFile(path, ForWriting, True)
    oQBVerify.WriteLine "The following files do not match between MANIFEST and QUICKBOOKS:"
    
    For Each rCell In Selection.Cells
        On Error Resume Next
        Err.Clear
        strFileNum = rCell.Offset(0, -27).Value
        strDutyAmt = rCell.Offset(0, -8).Value
        strQBDutyAmt = Application.WorksheetFunction.Index(Workbooks("DUTY.xlsx").Sheets("Sheet1").Range("I:I"), Application.WorksheetFunction.Match(strFileNum, Workbooks("DUTY.xlsx").Sheets("Sheet1").Range("e:e"), 0), 1)
        If Err.Number = 0 Then
            If strQBDutyAmt = strDutyAmt Then GoTo lbl_DUTYMATCH
                oQBVerify.WriteLine strFileNum & " MFST: " & strDutyAmt & " QB: " & strQBDutyAmt
        Else
            oQBVerify.WriteLine strFileNum & " MFST: " & strDutyAmt & " QB: NOT FOUND"
        End If
        
lbl_DUTYMATCH:
    Next rCell

'-----------------------UNRESOLVED SCRIPT------------------------------------
'----Need to check if value in each used cell in DUTY.xlsx column E exists in the *selected range* strFileNum, or rCell.Offset(0,-27); if it does, goto next; otherwise output text alert
    oQBVerify.WriteLine "The following files are on the QB report but not the Manifest:"
    For Each aCell In Workbooks("DUTY.xlsx").Sheets("Sheet1").Range("e3:e")   'HOW TO MAKE SURE ONLY USED CELLS GET PROCESSED?? something with .end(xlUp)?, not sure how to structure
        On Error Resume Next
        Err.Clear
        '???? ---------------HOW TO INDEX/MATCH OR VLOOKUP AGAINST ONLY THE SELECTED RANGE (OFFSET AS NOTED ABOVE)????
        If Err.Number = 0 Then GoTo lbl_FILEMATCH
        Else
            oQBVerify.WriteLine "QB : " & aCell.Value & " MFST: NOT FOUND"
        End If
lbl_FILEMATCH:
    Next aCell
'-----------------------UNRESOLVED SCRIPT------------------------------------

    oQBVerify.WriteLine "END REPORT"
    oQBVerify.Close
    
End Sub

[bifjamod]

Update: I've made some progress, as follows, with the unresolved script. The updated code will successfully output all file numbers in the desired external range IF I comment out the error handling. When I run the code with the error handling, no files get output as desired, even though I have one that is most certainly NOT in the original selection, as a means of testing.

'-----------------------UNRESOLVED SCRIPT------------------------------------
'----Need  to check if value in each used cell in DUTY.xlsx column E exists in the  *selected range* strFileNum, or rCell.Offset(0,-27); if it does, goto  next; otherwise output text alert
    oQBVerify.WriteLine "The following files are on the QB report but not the Manifest:"
    lastrow = Workbooks("DUTY.xlsx").Sheets("Sheet1").Range("E" & Rows.Count).End(xlUp).Row
    Set rngDuty = Workbooks("DUTY.xlsx").Sheets("Sheet1").Range("e3:E" & lastrow)
    Set rngMonthly = Selection
    
    For Each aCell In rngDuty
         strQBFile = Application.WorksheetFunction.Index(rngMonthly,  Application.WorksheetFunction.Match(aCell.Value, rngMonthly.Offset(0 -  27), 0), 1)
        
        On Error Resume Next
        Err.Clear
        If Err.Number = 0 Then GoTo lbl_FILEMATCH
        oQBVerify.WriteLine "QB : " & aCell.Value & " MFST: NOT FOUND"
       
        
lbl_FILEMATCH:
    Next aCell
'-----------------------UNRESOLVED SCRIPT------------------------------------

[bifjamod]

And still more progress; the problem now is this: I can only get the output to return files that are found, when what I want is files that are NOT found. I've tried several variations on this latest script with no joy - I either get only the found files, or nothing at all.

'-----------------------UNRESOLVED SCRIPT------------------------------------
'----Need to check if value in each used cell in DUTY.xlsx column E exists in the *selected range* strFileNum, or rCell.Offset(0,-27); if it does, goto next; otherwise output text alert
    oQBVerify.WriteLine "The following files are on the QB report but not the Manifest:"
    lastrow = Workbooks("DUTY.xlsx").Sheets("Sheet1").Range("E" & Rows.Count).End(xlUp).Row
    Set rngDuty = Workbooks("DUTY.xlsx").Sheets("Sheet1").Range("e3:E" & lastrow)
    Set rngMonthly = Selection.Offset(0, -27)
    Dim Found As Range

    For Each aCell In rngDuty
        Set Found = rngMonthly.Find(what:=aCell.Value, LookIn:=xlValues, lookat:=xlWhole)
        If Found Is Nothing Then
            oQBVerify.WriteLine "QB : " & Found & " VLOOKUP: NOT FOUND"
        Else
            oQBVerify.WriteLine "QB : " & Found & " VLOOKUP: FOUND"
        End If
        
lbl_FILEMATCH:
    Next aCell
'-----------------------UNRESOLVED SCRIPT------------------------------------

[bifjamod]

Persistence pays off, even when the crowd doesn't! The working solution:

 Dim aCell As Range
    Dim rngDuty As Range
    Dim rngMonthly As Range
    Dim Found As Range
    oQBVerify.WriteLine "The following files are on the QB report but not the Manifest:"
    lastrow = Workbooks("DUTY.xlsx").Sheets("Sheet1").Range("E" & Rows.Count).End(xlUp).Row
    Set rngDuty = Workbooks("DUTY.xlsx").Sheets("Sheet1").Range("e3:E" & lastrow)
    Set rngMonthly = Selection.Offset(0, -27)
    
    For Each aCell In rngDuty
        Set Found = rngMonthly.Find(what:=aCell.Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not Found Is Nothing Then
            GoTo lbl_FILEMATCH
        Else
            oQBVerify.WriteLine "QB : " & aCell.Value & " MFST: FILE NOT FOUND"
        End If

lbl_FILEMATCH:
    Next aCell