Vlookup double criteria and Date Results

[ruci1225]

I have Worksheet A that has a phone number field and a Date/Time field for Lead data, I then have Worksheet B that has phone number field and a Date/Time field for call data.

I did a vlookup matching phone number between the sheets and bringing back Date/time from sheet B to a new column in Worksheet A called contact time.

Since the call data is sorted older>new it bring back the first match.

this works fine when I have data that is both on the same day as it finds the first match.

The issue is as follows:
When I have data for a 30 day period, and under the following scenrio the lookup is not accurate.

Lead Date is 4/7/2011 (worksheet A) and 10AM (worksheet A)

The Vlookup brings back the first match by phone number and the Lead Date is 4/1/2011 as this person was called for another reason prior to recieving the lead for a new program inquiry. This is not accurate.
I am looking to find the closest match of date/time in worksheet B with Worksheet A so that I can see how long it took for the phone number to be contacted after the Lead Date (But the first time after the lead date)

i hope this makes sense

 

[ruci1225]

I am now getting the following: ( I will always have a different number of rows depending on the imported data whichchanges)


Run-Time Error 1004
Autofill Method of Range Class Failed

Selection.FormulaArray = _
"=MIN(IF('I3'!R1C10:R60000C10=RC[-2],IF('I3'!R1C11:R60000C11>=RC[-1],'I3'!R1C11:R60000C11)))"
Selection.AutoFill Destination:=Range("$AT$1:$AT$6799")

 

[Peter_SSs]

The problem most likely relates to what is selected at this point in the code, but we don't know what that is. Generally you do not need to select things to work with them in vba and selecting slows your code.

If your code isn't too long, perhaps you could post it and somebody may be able to offer suggestions as to the particular error you have encountered and/or a more efficient way to insert the formulas in the sheet. If posting code, please use Code tags as described in my signature block below.

If you are unable to post your code, at least tell us what cell(s) are selected in the sheet when that error occurs.