VBA macro to map variables against date

Posted on 2012-09-21
Medium Priority
Last Modified: 2012-10-07
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
Question by:Excellearner
  • 2
  • 2

Expert Comment

ID: 38421170
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))

Author Comment

ID: 38421241
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

Accepted Solution

Beneford earned 2000 total points
ID: 38421254
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.

Author Closing Comment

ID: 38471782
Hi Beneford,

Thank you for the formula, it worked fantastic.

Sorry for not getting back any earlier.

Thank you

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question