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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:u21VIv%(E-Mail Removed)...
> 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 ***

Chris,

You wrote

"it is 170 days from the date showing in column AQ."

I am assuming "it" means _today_. Could it be a date in another cell?

If "from the date" means "if it (today) is 170 or fewer days _before_ the
date in AQ3"

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


If "from the date" means "if it (today) is 170 or more days _after_ the
date in AQ3"

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

If you only want 'it' to be exactly 170 days before or after, remove the <
part of the comparison <=

If neither of those is what you actually want, give me some example dates
and when you want TRUE to show up....

Bernie
MS Excel MVP


"Chris" <(E-Mail Removed)> wrote in message
news:Owp3mQ$(E-Mail Removed)...
> 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 ***

Hi Bernie, Oops, I am sorry I have not made myself very clear -
sometimes it is dificult to explain what you want

when a picture speaks a thousand words. Here is an example: (please
note that today's date is not used in the

equation). We need the formula to be in column AR for the following
example:

In column X at cell X3 is the text: "VACANT". In column AQ at cell AQ3
is the date: 01 Dec 09. We need to know 170

days out from the date showing in column AQ (which in this example is:
01 Dec 09), that the corresponding value in

cell X3 contains the text: "VACANT" (which for this example it does).
Thus, the corresponding cell at AR3 (using

your formula) would be popluated with TRUE.

In column X at cell X4 is the text: "FILLED". In column AQ at cell AQ4
is the date: 05 Dec 09. We need to know 170

days out from the date showing in column AQ (which in this example is:
05 Dec 09), that the corresponding value in

cell X4 contains the text: "FILLED" (which for this example it does).
Thus, the corresponding cell at AR4 (using

your formula) would be popluated with FALSE.

In column X at cell X5 is the text: "VACANT". In column AQ at cell AQ5
is the date: 12 Dec 09. We need to know 170

days out from the date showing in column AQ (which in this example is:
12 Dec 09), that the corresponding value in

cell X5 contains the text: "VACANT" (which for this example it does).
Thus, the corresponding cell at AR5 (using

your formula) would be popluated with TRUE.

Does this example help clear things up?

Kind regards,

Chris.



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

Chris,

You say that today's date is not relevant.... what date is relevant?

AQ3 is 01 Dec 2009.

170 days before 01 Dec 2009 is 14 Jun 2009. Let's say that X3 is "VACANT"

If you are looking at the workbook on 13 Jun 2009 (171 days before 01 Dec
2009), what do you want to see in AR3? TRUE - or - FALSE?

If you are looking at the workbook on 14 Jun 2009 (Exactly 170 days before
01 Dec 2009), what do you want to see in AR3? TRUE - or - FALSE?

If you are looking at the workbook on 15 Jun 2009 (169 days before 01 Dec
2009), what do you want to see in AR3? TRUE - or - FALSE?

WHEN do you actually want to see TRUE in cell AR3?

Bernie


"Chris" <(E-Mail Removed)> wrote in message
news:OQxEi1$(E-Mail Removed)...
> Hi Bernie, Oops, I am sorry I have not made myself very clear -
> sometimes it is dificult to explain what you want
>
> when a picture speaks a thousand words. Here is an example: (please
> note that today's date is not used in the
>
> equation). We need the formula to be in column AR for the following
> example:
>
> In column X at cell X3 is the text: "VACANT". In column AQ at cell AQ3
> is the date: 01 Dec 09. We need to know 170
>
> days out from the date showing in column AQ (which in this example is:
> 01 Dec 09), that the corresponding value in
>
> cell X3 contains the text: "VACANT" (which for this example it does).
> Thus, the corresponding cell at AR3 (using
>
> your formula) would be popluated with TRUE.
>
> In column X at cell X4 is the text: "FILLED". In column AQ at cell AQ4
> is the date: 05 Dec 09. We need to know 170
>
> days out from the date showing in column AQ (which in this example is:
> 05 Dec 09), that the corresponding value in
>
> cell X4 contains the text: "FILLED" (which for this example it does).
> Thus, the corresponding cell at AR4 (using
>
> your formula) would be popluated with FALSE.
>
> In column X at cell X5 is the text: "VACANT". In column AQ at cell AQ5
> is the date: 12 Dec 09. We need to know 170
>
> days out from the date showing in column AQ (which in this example is:
> 12 Dec 09), that the corresponding value in
>
> cell X5 contains the text: "VACANT" (which for this example it does).
> Thus, the corresponding cell at AR5 (using
>
> your formula) would be popluated with TRUE.
>
> Does this example help clear things up?
>
> Kind regards,
>
> Chris.
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

Hi Bernie,

AQ3 is 01 Dec 2009.

170 days before 01 Dec 2009 is 14 Jun 2009. let us say that X3 is
"VACANT".

If we are looking at the workbook on 13 Jun 2009 (171 days before 01 Dec
2009), then we would like to see in AR3 FALSE.

If we are looking at the workbook on 14 Jun 2009 (170 days before 01 Dec
2009), then we would like to see in AR3
TRUE.

If we are looking at the workbook on 15 Jun 2009 (169 days before 01 Dec
2009), then we would like to see in AR3
TRUE.

We would like to see TRUE in cell AR3 for all dates 141 to 170 days
prior to 01 Dec 2009 and where X3 still shows
"VACANT".

We undertake 5 separate checks on this workbook. For example;

Our first check is at approximatelty 170 days before 01 Dec 2009 to see
if there are any cells in column X that are populate with the text:
"VACANT".

So, if we were to check the workbook at let's say 167 days before 01 Dec
2009 and cell X3 is still populated with
the text: VACANT", then AR3 should say: TRUE.

If however, on 169 days before 01 Dec 2009, cell X3 is populated with
the text: "FILLED", then AR3 should say:
FALSE.

We try to check the workbook on time but due to public holidays and
weekends, it is not always practical to check it exactly 170 days
before.

The same goes for 140 days before 01 Dec 2009. We would like to see
TRUE in cell AR3 for all dates 111 to 140 days
prior to 01 Dec 2009 and where X3 still shows "VACANT".

I hope this helps - it's difficult to explain - please feel free to ask
more questions,

I really appreciate your kind help,

Cheers,

Chris.



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

Chris,

In AR1, enter 170.
In AS1, enter 140
In AT1, enter 110
In AU1, enter 80
In AV1, enter 50
In AW1, enter 20
In AX1, enter 0

In AR3, enter the formula

=IF($AQ3="","",IF(AND($AQ3<=TODAY()+AR$1,$AQ3>TODAY()+AS$1,$X3="VACANT"),TRUE,""))
Then copy AR3 to AT3:AW3, and then copy AR3:AW3 down for as many rows as you
need.

HTH,
Bernie
MS Excel MVP


"Chris" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Bernie,
>
> AQ3 is 01 Dec 2009.
>
> 170 days before 01 Dec 2009 is 14 Jun 2009. let us say that X3 is
> "VACANT".
>
> If we are looking at the workbook on 13 Jun 2009 (171 days before 01 Dec
> 2009), then we would like to see in AR3 FALSE.
>
> If we are looking at the workbook on 14 Jun 2009 (170 days before 01 Dec
> 2009), then we would like to see in AR3
> TRUE.
>
> If we are looking at the workbook on 15 Jun 2009 (169 days before 01 Dec
> 2009), then we would like to see in AR3
> TRUE.
>
> We would like to see TRUE in cell AR3 for all dates 141 to 170 days
> prior to 01 Dec 2009 and where X3 still shows
> "VACANT".
>
> We undertake 5 separate checks on this workbook. For example;
>
> Our first check is at approximatelty 170 days before 01 Dec 2009 to see
> if there are any cells in column X that are populate with the text:
> "VACANT".
>
> So, if we were to check the workbook at let's say 167 days before 01 Dec
> 2009 and cell X3 is still populated with
> the text: VACANT", then AR3 should say: TRUE.
>
> If however, on 169 days before 01 Dec 2009, cell X3 is populated with
> the text: "FILLED", then AR3 should say:
> FALSE.
>
> We try to check the workbook on time but due to public holidays and
> weekends, it is not always practical to check it exactly 170 days
> before.
>
> The same goes for 140 days before 01 Dec 2009. We would like to see
> TRUE in cell AR3 for all dates 111 to 140 days
> prior to 01 Dec 2009 and where X3 still shows "VACANT".
>
> I hope this helps - it's difficult to explain - please feel free to ask
> more questions,
>
> I really appreciate your kind help,
>
> Cheers,
>
> Chris.
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***