VBA Code to compare Dates, then run macro to match colums

[Airstar]

Hi, Hope I can get some help with adding code to loop through the dates in column A of Sheet1 & compare them to the dates in Column A, Sheet2. If the date on Sheet2 is greater than Sheet1 run the MatchColumns macro.

Sheet1 accumulates jobs with recommendations through out the year. Sheet2 accumulates completed recommendations. Any row on Sheet1 without a match needs to be followed up on. Duplications are appearing as highlighted on row 15. Since the job on row 15 took place on 1/24/19, the recommendation could not have been completed prior to that date, 12/19/2018

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Excel 2010
	A	B	C	G	H	I	J	K	O	P	Q	R
1	Sheet1 Data					Sheet2 Data
2	Sch Dte	Job NM	Last NM	Description	NO MATCH	Sch Dte	Job NM	Last NM	Description
3	12/1/2018	13738 - Gil Sub	Gillman	Replace Thermostat $456, Compressor & Contactor (will email Quote)	NO MATCH
4	12/3/2018	13740 - Dary, Haish	Dary	Estimate to replace equipment. Quote emailed	NO MATCH
5	12/3/2018	13745 - McCray, Vic	McCray	DS-Replace Condenser Fan Motor $700 ($595) (SCH)	RECONCILED	12/4/2018	13746 - McCray, Vic	McCray	Perform Recommend work on- Condenser Fan Motor
6	12/8/2018	13772 - Dell, Erik	Del	Clear roof Cap $193	NO MATCH
7	12/14/2018	13782 - Ellen, Rob	Ellen	Replace Gas Valve $668 ($567) (SCH)	RECONCILED	12/17/2018	13797 - Ellen, Rob	Ellen	Perform Recommend work on- Gas Valve
8	12/14/2018	13765 - Palo, Marc	Palo	Replace Blower motor capacitor $208, Hard star kit $327, Pull & clean blower assembly $275, or Replace Blower motor $665. Upgrade equipment	NO MATCH
9	12/17/2018	13799 - Kane, Cage	Kane	Replace unit with York package unit. $5,900	NO MATCH
10	12/18/2018	13790 - Moss, Ren	Moss	Estimate to replace equipment or ductwork. Will email quote when we get pricing from electrician. Quote emailed on 12/19/2019 for estimate.	RECONCILED	12/19/2018	13774 - Moss, Ren 2925	Moss	Performed recommend work on Ductwork
11	12/18/2018	13796 - Kell, Suz	Kell	Estimate to replace equipment. Quoted-Standard York system $7,778 less 10% discount $(777) for a net price of $7001. York condenser, furnace, evaporator coil, and thermostat. 10 Yr Part, 10 Yr compressor, 1 Yr labor warranty. (Not covered under warranty- dispatch fees, Refrigerant lines, refrigerant, T-stat wire, drain lines, ductwork and insulation.)	NO MATCH
12	1/16/2019	13846 - Benny, Donna	Benny	Rewire air handler and thermostat $182	NO MATCH
13	1/18/2019	13840 - Ender, Bett	Enderle	Replace Reversing Valve Solenoid (Warranty) $214 -Order Part	NO MATCH
14	1/23/2019	13880 - Laba, Dave	Laba	Replace Hot Surface Ignitor $227 ($193) (have to order part and return)	RECONCILED	1/24/2019	13940 - Laba, Dave	Laba	Performed recommend work on-Ignitor
15	1/23/2019	13877 - Moss, Ren 2925	Moss	Estimate to replace ductwork. Will sanitize ductwork, seal connections, replace registers, clean furnace and inspect system $1195. Customer will call us when the house is ready for us to do the job.	RECONCILED	12/19/2018	13774 - Moss, Ren 2925	Moss	Performed recommend work on Ductwork
16	1/24/2019	13882 - More, Michell	More	Replace inducer draft motor $566 ($481) Customer will not replace selling	NO MATCH
Sheet1

I've added the following code, but I', getting a run time error

[<code>Sub CompareDates() </code><code> </code><code>If Sheet1.Range("A1") > Sheet2.Range("A1") Then
Run "MatchColumns"
End If
End Sub]

Any help would greatly be appreciated by this really novice VBA/Excel user!

Thanks in advance
</code>[/FONT]<strike></strike>

 

[JLGWhiz]

See if this works for you

Sub t()
Dim i As Long
    For i = 3 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Row 'If data starts on row other than 3, change accordingly.
        If Sheet1.Cells(i, 1).Value < Sheet2.Cells(i, 1).Value Then
            MatchColumns
        End If
    Next
End Sub

 

[Airstar]

JLGWhiz, thanks so much for your reply. Still getting the same result. Your code runs the match macro, but it's not evaluating the dates prior to the run. I've double checked to be sure Col A on both sheets is formatted as a date; Col I on Sheet1 is also formatted as date. I've also tried formatting as a number, but I'm still getting the same result. What do you think could be the problem?

Thanks again for your invaluable assistance!

 

[JLGWhiz]

I set up two sheets with dates in column A beginning at row 3 on both. Some cells were left blank on sheet 2 as in the OP example. I used a fake macro named 'MatchColumns" with both codes being in the standard code module1. The code ran as expected with no errors. Where dates in sheet 1 were less than dates in sheet 2, the fake macro ran, blanks were bypassed as well as any dates equal to or less than the corresponding date on sheet 1. What is the error you are getting and which line of code is highlighted when you click the 'Debug' button? Your error might be in the called macro.

 

[Airstar]

JLGWhiz, I am still getting the same result as shown in the example above. I'm not getting an error in the macro, but some of the results are incorrect. On Sheet1 row 15 there is a match dated 12/19/2018 (Sheet2 row 6) which is not greater than 1/23/19 (Sheet1 row 15). The match marco should not have run in this instance. The job dated 12/19/2018 correctly matched to Sheet1 row10 dated 12/18/2018.

How can I get the date comparison macro to run against each match?

Here is a copy of the data from Sheet2

Excel 2010
	A	B	C	G	H
1	Sch Date	Job Name	Last Name	Item Description	RECONCILED
2	12/3/2018	13692 - Dom, Tam	Dom	Perform Recommend work on capacitors	RECONCILED
3	12/4/2018	13746 - McCray, Vic	McCray	Perform Recommend work on- Condenser Fan Motor	RECONCILED
4	12/5/2018	13744 - Warden, Vic	Warden	Performed recommend work on- Circuit Board	RECONCILED
5	12/5/2018	13605 - Yung, Rob	Yung	Perform Recommend work on RAFG	RECONCILED
6	12/19/2018	13774 - Moss, Ren 2925	Moss	Performed recommend work on Ductwork	RECONCILED
7	12/14/2018	13747 - Thomas, L.	Thomas	Perform Recommend work on- Arma Flex	RECONCILED
8	12/17/2018	13797 - Ellen, Rob	Ellen	Perform Recommend work on- Gas Valve	RECONCILED
9	12/18/2018	13783 - Dragoon, LLC	Dragoon	Performed Recommended work on- Blower, Belt, Capacitor, Drain Line, Compressor	RECONCILED
10	12/18/2018	13800 - Brywn, Glo	Brown	Performed Recommended work on Master-Gas valve/burners	RECONCILED
11	1/11/2019	13830 - Biech, Rob	Biech	Perform Recommend work - Pull & cleaned Evap Coil. Changed Filters	RECONCILED
12	1/24/2019	13940 - Laba, Dave	Laba	Performed recommend work on-Ignitor	RECONCILED
13	1/28/2019	13837 - Dimm, Cline	Dimm	Perform Recommend Work on Blower Wheel Assembly $275, Replaced Blower Motor Capacitor $177, Put Armor-flex $55	RECONCILED
14	1/29/2019	13884 - Ella, Ron	Ella	Perform Recommend work on- Clean Blower Assembly	RECONCILED
15
Sheet2

And here is the code I am using to match columns

[Sub MatchColumns()

Dim i, total, fRow As Integer
Dim found As Range

total = Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To total
answer1 = Worksheets(1).Range("C" & i).Value
Set found = Worksheets(2).Columns("C:C").Find(what:=answer1) 'finds a match

If found Is Nothing Then
Worksheets(1).Range("H" & i).Value = "NO MATCH"
Else
fRow = Sheets(2).Columns("C:C").Find(what:=answer1).Row
Worksheets(1).Range("I" & i).Value = Worksheets(2).Range("A" & fRow).Value
Worksheets(1).Range("J" & i).Value = Worksheets(2).Range("B" & fRow).Value
Worksheets(1).Range("K" & i).Value = Worksheets(2).Range("C" & fRow).Value
Worksheets(1).Range("L" & i).Value = Worksheets(2).Range("D" & fRow).Value
Worksheets(1).Range("M" & i).Value = Worksheets(2).Range("E" & fRow).Value
Worksheets(1).Range("N" & i).Value = Worksheets(2).Range("F" & fRow).Value
Worksheets(1).Range("O" & i).Value = Worksheets(2).Range("G" & fRow).Value
Worksheets(1).Range("H" & i).Value = Worksheets(2).Range("H" & fRow).Value

End If
Next i

End Sub]

Thanks so much for your help!