VBA Multiple Criteria Multiple Sheet Reconciliation

Kat11

New Member
Joined
Sep 15, 2017
Messages
1
Hi! I’m a complete novice to excel formulas and VBA. I’m not even sure if excel can do what I’m trying. I've checked all of the posts I could find and nothing seems to fit.

I’m trying to have excel compare the details on sheet1 against the details on sheet2 to return the sheet2 row number if a match is found in column A and a true, false or an #n/a response for the amount in column C of that same row. If nothing matches, it should return an error #n/a. Some of my worksheets can have up to 25,000 lines of data and the account numbers in column A may appear more than once with different amounts in column C.

This is what I’m starting with. I have 2 sheets in the same workbook. Each sheet has 3 columns. Both sheets are sorted in ascending order on column A. Column A has an account number which can be any combination of numbers, letters and dashes/slashes. Column B has an account name which again can be numbers, letters, and dashes/slashes. Column C has an amount.
What I’m running into is that MATCH returns the row number in column A and VLOOKUP returns a true/false or n/a in column C, with both based on the first instance of the same account number in column A. Is there a way to have excel continue looking down the columns for other matches until the end of the data?

This is the code I have. I’m not sure if it can be tweaked or if it needs to be something completely different. As I said, I’m a complete novice. I'm only starting to learn formulas and don’t know of others yet that may be able to produce the results I’m looking for.

Code:
Sub REC()
    Sheets("Sheet1").Select
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=MATCH(RC[-3],Sheet2!C[-3],0)"
    Selection.NumberFormat = "0_);[Red](0)"
    Range("E2").Select
    ActiveCell.FormulaR1C1 =
        "=VLOOKUP(RC1,Sheet2!C[-4]:C[26],3,FALSE)=Sheet1!RC[-2]"
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    Range("D2").AutoFill Destination:=Range("D2:D" & lastrow)
    Range("E2").AutoFill Destination:=Range("E2:E" & lastrow)
End sub

This is an example of what it’s returning.
Sheet 1
Col A Col B Col C Col D Col E

Acct # Name Amt #? Amt?
X-1 X Inc. $25.00 2 True
X-1 X Inc. $10.00 2 False
X-1 X Inc. $17.00 2 False
Z-2 Z Ltd $45.00 #N/A #N/A

Sheet 2
Col A Col B Col C
Acct # Name Amt
X-1 X Inc. $25.00
X-1 X Inc. $17.00
X-1 X Inc. $10.00

Could someone possibly help me figure out what I’m missing?
Thank you!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

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
Back
Top