Hello, could someone please help me with the following? I need a macro
that looks up column AQ (a date column).

I then need the macro to calculate the date five-months prior to the
dates in range AQ3 : AQ65000.

With the new calculated date (five-months prior to date) - I need to
determine if the associated values in column X contain the text
"VACANT".

So I need the macro to lookup column X (a text column) and if any of the
cells in range X3 to X65000 contain the text "VACANT" then that record
needs to be copied to another open workbook named 5 Months Out.xls, and
onto worksheet named: 5 Months Out. The copied rows need to be pasted
onto the new worksheet starting at cell: A3.

For example: cell AQ5 contains the date: 01-Dec-09. The date
five-months prior to this would be: 01-Nov-09. In Column X there are
cells in range X3:X65000 that contain the text "VACANT". These are
located in cells: X5 and X24. Thus, the entire rows (row 5 and row 24)
need to be copied to the open workbook named 5 Months out.xls onto
worksheet named: 5 Months Out into cell A3 (all of row 3).

I am using Excel 2003 with Win XP.

Any help in this macro would be greatly appreciated.

Kind regards,

Chris.



*** Sent via Developersdex http://www.developersdex.com ***

Chris,

Just a clarification:
For example: cell AQ5 contains the date: 01-Dec-09. The date
five-months prior to this would be: 01-Nov-09.

Five months prior to 01 Dec 09 is 01 July 09. But 5 months from today is 28 Oct 09 - which date do
you want to use as the basis of your comparison?

Anyway, once you decide, the way to approach this problem is not to use a macro but to have a column
of formulas:

=AND(AQ3 = DATE(YEAR(TODAY()),MONTH(TODAY())+5,DAY(TODAY())), X3="VACANT")

Copied down to match your data set, then apply a data filter on that column, choose TRUE and then
copy only the rows that are visible after the filtering.

HTH,
Bernie
MS Excel MVP


"Chris" wrote in message ...
Hello, could someone please help me with the following? I need a macro
that looks up column AQ (a date column).

I then need the macro to calculate the date five-months prior to the
dates in range AQ3 : AQ65000.

With the new calculated date (five-months prior to date) - I need to
determine if the associated values in column X contain the text
"VACANT".

So I need the macro to lookup column X (a text column) and if any of the
cells in range X3 to X65000 contain the text "VACANT" then that record
needs to be copied to another open workbook named 5 Months Out.xls, and
onto worksheet named: 5 Months Out. The copied rows need to be pasted
onto the new worksheet starting at cell: A3.

For example: cell AQ5 contains the date: 01-Dec-09. The date
five-months prior to this would be: 01-Nov-09. In Column X there are
cells in range X3:X65000 that contain the text "VACANT". These are
located in cells: X5 and X24. Thus, the entire rows (row 5 and row 24)
need to be copied to the open workbook named 5 Months out.xls onto
worksheet named: 5 Months Out into cell A3 (all of row 3).

I am using Excel 2003 with Win XP.

Any help in this macro would be greatly appreciated.

Kind regards,

Chris.



*** Sent via Developersdex http://www.developersdex.com ***

Hi Bernie, thanks for you help - very much appreciated. I sought
clarification from my work supervisor as follows:

Column AQ is headed: "Planned date out of country" (cell: AQ2). Column
X is headed: "Position" (cell X2). Not all

the cells in column AQ are populated with a date.

We need know the following please:

170 days out from the dates showing in column AQ all those records that
are populated with the text "VACANT" in column X. If this condition is
true, then the corresponding cell in column AR needs to be populated
with the result of true. Column AR is headed: "170 Days Out".

140 days out from the dates showing in column AQ all those records that
are populated with the text "VACANT" in
column X. If this condition is true, then the corresponding cell in
column AS needs to be populated with the result of true. Column AR is
headed: "140 Days Out".

110 days out from the dates showing in column AQ all those records that
are populated with the text "VACANT" in
column X. If this condition is true, then the corresponding cell in
column AT needs to be populated with the result of true. Column AR is
headed: "110 Days Out".

80 days out from the dates showing in column AQ all those records that
are populated with the text "VACANT" in
column X. If this condition is true, then the corresponding cell in
column AU needs to be populated with the result of true. Column AR is
headed: "80 Days Out".

50 days out from the dates showing in column AQ all those records that
are populated with the text "VACANT" in
column X. If this condition is true, then the corresponding cell in
column AV needs to be populated with the result of true. Column AR is
headed: "50 Days Out".

I hope this clarifies what we need. If not, please do not hesitate to
contact me.

Many thanks,

Chris.



*** Sent via Developersdex http://www.developersdex.com ***

Chris,

I would use something like this in AR3:

=IF(AQ3="","",IF(AND(AQ3<=TODAY()+170,X3="VACANT") ,TRUE,""))

Change the 170 to 140 for use in AS3, and 110 for using in AT3.... etc.
Then copy those formulas down the column to match your data set.

And by 'days out' I hope you mean 170 days from today, etc.

HTH,
Bernie
MS Excel MVP



"Chris" wrote in message
...
Hi Bernie, thanks for you help - very much appreciated. I sought
clarification from my work supervisor as follows:

Column AQ is headed: "Planned date out of country" (cell: AQ2). Column
X is headed: "Position" (cell X2). Not all

the cells in column AQ are populated with a date.

We need know the following please:

170 days out from the dates showing in column AQ all those records that
are populated with the text "VACANT" in column X. If this condition is
true, then the corresponding cell in column AR needs to be populated
with the result of true. Column AR is headed: "170 Days Out".

140 days out from the dates showing in column AQ all those records that
are populated with the text "VACANT" in
column X. If this condition is true, then the corresponding cell in
column AS needs to be populated with the result of true. Column AR is
headed: "140 Days Out".

110 days out from the dates showing in column AQ all those records that
are populated with the text "VACANT" in
column X. If this condition is true, then the corresponding cell in
column AT needs to be populated with the result of true. Column AR is
headed: "110 Days Out".

80 days out from the dates showing in column AQ all those records that
are populated with the text "VACANT" in
column X. If this condition is true, then the corresponding cell in
column AU needs to be populated with the result of true. Column AR is
headed: "80 Days Out".

50 days out from the dates showing in column AQ all those records that
are populated with the text "VACANT" in
column X. If this condition is true, then the corresponding cell in
column AV needs to be populated with the result of true. Column AR is
headed: "50 Days Out".

I hope this clarifies what we need. If not, please do not hesitate to
contact me.

Many thanks,

Chris.



*** Sent via Developersdex http://www.developersdex.com ***

Hi again Bernie, thanks so much for you excellent assistance - greatly
appreciated. It's probably my fault, but it is 170 days from the date
showing in column AQ. Would you be able to please tweak your formula
accordingly?

Many Thanks,

Chris.



*** Sent via Developersdex http://www.developersdex.com ***

there is a DATEDIFF function that you can use in VBA

"Chris" wrote in message
...
Hello, could someone please help me with the following? I need a macro
that looks up column AQ (a date column).

I then need the macro to calculate the date five-months prior to the
dates in range AQ3 : AQ65000.

With the new calculated date (five-months prior to date) - I need to
determine if the associated values in column X contain the text
"VACANT".

So I need the macro to lookup column X (a text column) and if any of the
cells in range X3 to X65000 contain the text "VACANT" then that record
needs to be copied to another open workbook named 5 Months Out.xls, and
onto worksheet named: 5 Months Out. The copied rows need to be pasted
onto the new worksheet starting at cell: A3.

For example: cell AQ5 contains the date: 01-Dec-09. The date
five-months prior to this would be: 01-Nov-09. In Column X there are
cells in range X3:X65000 that contain the text "VACANT". These are
located in cells: X5 and X24. Thus, the entire rows (row 5 and row 24)
need to be copied to the open workbook named 5 Months out.xls onto
worksheet named: 5 Months Out into cell A3 (all of row 3).

I am using Excel 2003 with Win XP.

Any help in this macro would be greatly appreciated.

Kind regards,

Chris.



*** Sent via Developersdex http://www.developersdex.com ***