asked on

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

Beneford

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))

excel learner

ASKER

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

ASKER CERTIFIED SOLUTION

Beneford

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

excel learner

ASKER

Hi Beneford,

Thank you for the formula, it worked fantastic.

Sorry for not getting back any earlier.

Thank you