PDA

View Full Version : [SOLVED] Searching selected range (offset) for values in external workbook range



bifjamod
04-14-2016, 01:00 PM
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
04-15-2016, 01:19 PM
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
04-15-2016, 03:07 PM
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
04-15-2016, 03:42 PM
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