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

Report
Posts
7
Registration date
Tuesday January 20, 2015
Status
Member
Last seen
January 26, 2015
-
 Babu546 -
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


1 reply

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.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
7
Registration date
Tuesday January 20, 2015
Status
Member
Last seen
January 26, 2015

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
>
Posts
7
Registration date
Tuesday January 20, 2015
Status
Member
Last seen
January 26, 2015

The original code worked.
I have made a couple of 'enhancments' to help you.

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