DUE DATE REMINDER IN EXCEL TO NEW SHEET? [Solved/Closed]

Babu546 7 Posts Tuesday January 20, 2015Registration date January 26, 2015 Last seen - Jan 21, 2015 at 08:57 AM - Latest reply:  Babu546
- Jan 22, 2015 at 01:37 PM
Hello,


I Have a problem in this code

=INDEX(Master!$B$3:Master!$B$1067,MATCH(TODAY()+30,Master!$H$3:Master!$H$1067,0))

HERE I HAVE COLUMN " B " REPRESENTS " SOL_ID " WHICH IS A UNIQUE IN MASTER SHEET,

HERE COLUMN RANGE IS BETWEEN A3 TO H 1067

COLUMN " H " REPRESENTS THE DUE DATE FOR THE SOL_ID

NOTE: SAME DUE DATE IS THERE FOR MORE THAN ONE SOL_ID

WHEN I RUN THIS ABOVE CODE IT IS GIVING ONLY ONE SOL_ID BUT I WANT TOTAL SOL_IDS WHOSE DUE DATE IS AFTER 30 DAYS IN NEW SHEET

NOW MY MOTIVE IS TO PRINT/PASTE THE SOL_ID WHOSE DUE DATE IS AHEAD OF ONE MONTH IN ANOTHER SHEET

FOR EXAMPLE :-
I HAVE 3 SOL_IDS 522,645 & 1601 DUE ON AFTER 30 DAYS i.e =TODAY()+30

NOW I WANT TO PRINT/PASTE THESE IN TO ANOTHER SHEET SO THAT I CAN USE THESE UNIQUE SOL_IDS IN VLOOKUP AND GET THOSE ROWS IN NEW SHEET

I SAID THAT I AM GETTING ONLY ONE SOL_ID AND WHEN I USED IT IN VLOOKUP IT IS GIVING ME THE ERROR " A VALUE IS NOT AVAILABLE TO THE FORMULA OR FUNCTION "

ANY ASSISTANCE FOR THIS ISSUE IS GREATLY APPRECIATED....
THANKS IN ADVANCE


See more 

5 replies

Best answer
1
Thank you
That formula was only designed to give in a single result.

Rather that using a formula it is probably best to using a macro to filter the data instead.


Sub filterandcopy()
Dim dateoffset As Integer
Dim lastrow As Long

lastrow = Worksheets("MainSheet").Range("A" & Rows.Count).End(xlUp).Row

dateoffset = 30

d = Date + dateoffset

Worksheets("MainSheet").Range("$A$1:$H$" & lastrow).AutoFilter Field:=8, Criteria1:=">=" & d, Operator:=xlAnd
Worksheets("MainSheet").UsedRange.Copy Destination:=Sheets("DueDate").Range("A1")

Selection.AutoFilter

End Sub


In this example I have named the source data "MainSheet" and the sheet to where the data is pasted, "DueDate".

I have used a variable for the dateoffset, in this case the value is 30, it could be changed to some other value if needed. It could even be set to refer to a cell on a sheet that can be changed as and when required to save messing around with the code each time a change is required.

Just something to be aware of, the use of "UsedRange" could be problematic as it does select blank cells to the right and bottom if they have been used at some point.

Thank you, RayH 1

Something to say? Add comment

CCM has helped 1701 users this month

Babu546 7 Posts Tuesday January 20, 2015Registration date January 26, 2015 Last seen - Jan 22, 2015 at 03:20 AM
thank a lot sir for your response

But I am a beginner to Macros so I thought I will share my file with you so that I may explain you better
this is my file link

http://speedy.sh/q658J/Due-Date-Reminder.xls

any assistance will greatly appreciated....
Thanks in advance
RayH > Babu546 7 Posts Tuesday January 20, 2015Registration date January 26, 2015 Last seen - Jan 22, 2015 at 11:06 AM
The original code worked.
I have made a couple of 'enhancments' to help you.

http://speedy.sh/YB26j/babu-Due-Date-Reminder.xls
Babu546 7 Posts Tuesday January 20, 2015Registration date January 26, 2015 Last seen > RayH - Jan 22, 2015 at 12:05 PM
thanks a ton for your effort to make me happy see you
I very happy happy with your response mr.RayH
Thanks a lot