VBA macro to map variables against date

Dear experts
Need a vba macro which can map the date in column A in sheet 'data' to the intervals in column A and B of hseet criterial and then map the adjacent variable provided in column D of sheet criteria to results column in column C of sheet data
In actual sample, the date ranges will be different
Any  date in date range prior to value in A3 in this sheet should be termed as Past due
If the date column in sheet data has a non date field then the same shall be copied into the result column as well

Thank you
VBA-macro-to-map-variables-again.xlsx
ExcellearnerAsked:
Who is Participating?
 
BenefordConnect With a Mentor Commented:
Do you really need a Macro? I usually find formulae are easier where possible.

My formula replicates your 'result' column in the data sheet - see attached.
VBA-macro-to-map-variables-again.xlsx
0
 
BenefordCommented:
If the dates are continuous (ie no gaps) and in order, you can use:

=IF(ISERROR(LOOKUP(A3,Criteria!A:A,Criteria!D:D)),"Past due",IF(LOOKUP(A3,Criteria!A:A,Criteria!D:D)=0,A3,LOOKUP(A3,Criteria!A:A,Criteria!D:D)))

Put this formula in the result column first row and then replicate down the list.

It would be slightly more readable if you put LOOKUP(A3,Criteria!A:A,Criteria!D:D) in (eg) column D, and then this in column C:

 =IF(ISERROR(D3),"Past due",IF(D3=0,A3,D3))
0
 
ExcellearnerAuthor Commented:
Hi Benford.,

Thank you for the comment,

The sheet data is the source sheet for column D in sheet Criteria.

We need a vba macro for sheet data which uses the date range and date variable in criteria.

Kindly help

thank you
0
 
ExcellearnerAuthor Commented:
Hi Beneford,

Thank you for the formula, it worked fantastic.

Sorry for not getting back any earlier.

Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.