Hi,

I want to replace the following VBA code;

' guess 15-day date!
InBy_Box.Text = DateValue(Referral_Box.Text) + 21

Where 21 days are guessed from the referral date, with the following
equivlant formula;


=A2+IF(B$2=0,0,SIGN(B$2)*SMALL(IF((WEEKDAY(A2+SIGN (B$2)*(ROW(INDIRECT("1:"&ABS(B$2)*10))),2)<6)*ISNA (MATCH(A2+SIGN(B$2)*(ROW(INDIRECT("1:"&ABS(B$2)*10 ))),C$2:C$3,0)),ROW(INDIRECT("1:"&ABS(B$2)*10))),A BS(B$2)))

This Array formula is work around for the Workday function if no analysis
tool pak can be included, however i need to know how this should be
transcribed into VBA, so that upon entering the referral date in a form the
future date (workdays) automatically calculates. Please help i'm really stuck
and do not know much about this line of VBA.

Thanks In Advance

Do something like this pseudo code.

Dim cnt as Long, dt as Date
dt = DataValue("01/01/2006")
do while cnt < 15
dt = dt + 1
if weekday(dt,2) < 6 then cnt = cnt + 1
Loop

--
Regards,
Tom Ogilvy



"sdg8481" wrote in message
...
Hi,

I want to replace the following VBA code;

' guess 15-day date!
InBy_Box.Text = DateValue(Referral_Box.Text) + 21

Where 21 days are guessed from the referral date, with the following
equivlant formula;


=A2+IF(B$2=0,0,SIGN(B$2)*SMALL(IF((WEEKDAY(A2+SIGN (B$2)*(ROW(INDIRECT("1:"&ABS(B$2)*10))),2)<6)*ISNA (MATCH(A2+SIGN(B$2)*(ROW(INDIRECT("1:"&ABS(B$2)*10 ))),C$2:C$3,0)),ROW(INDIRECT("1:"&ABS(B$2)*10))),A BS(B$2)))

This Array formula is work around for the Workday function if no analysis
tool pak can be included, however i need to know how this should be
transcribed into VBA, so that upon entering the referral date in a form
the
future date (workdays) automatically calculates. Please help i'm really
stuck
and do not know much about this line of VBA.

Thanks In Advance

Hi,

Thanks for this but i can't seem to get it working, and i don't think it
takes into account holidays etc..... Also i also need it to calculate 13 and
8 working days in advance which obviously isn't so easy as 15. what does the
dt represent. Is it not possible to to use the earlier formula in VBA. Thanks

"Tom Ogilvy" wrote:

Do something like this pseudo code.

Dim cnt as Long, dt as Date
dt = DataValue("01/01/2006")
do while cnt < 15
dt = dt + 1
if weekday(dt,2) < 6 then cnt = cnt + 1
Loop

--
Regards,
Tom Ogilvy



"sdg8481" wrote in message
...
Hi,

I want to replace the following VBA code;

' guess 15-day date!
InBy_Box.Text = DateValue(Referral_Box.Text) + 21

Where 21 days are guessed from the referral date, with the following
equivlant formula;


=A2+IF(B$2=0,0,SIGN(B$2)*SMALL(IF((WEEKDAY(A2+SIGN (B$2)*(ROW(INDIRECT("1:"&ABS(B$2)*10))),2)<6)*ISNA (MATCH(A2+SIGN(B$2)*(ROW(INDIRECT("1:"&ABS(B$2)*10 ))),C$2:C$3,0)),ROW(INDIRECT("1:"&ABS(B$2)*10))),A BS(B$2)))

This Array formula is work around for the Workday function if no analysis
tool pak can be included, however i need to know how this should be
transcribed into VBA, so that upon entering the referral date in a form
the
future date (workdays) automatically calculates. Please help i'm really
stuck
and do not know much about this line of VBA.

Thanks In Advance

s =
"A2+IF(B$2=0,0,SIGN(B$2)*SMALL(IF((WEEKDAY(A2+SIGN (B$2)*(ROW(INDIRECT(""1:""&ABS(B$2)*10))),2)<6)*IS NA(MATCH(A2+SIGN(B$2)*(ROW(INDIRECT(""1:""&ABS(B$2 )*10))),C$2:C$3,0)),ROW(INDIRECT(""1:""&ABS(B$2)*1 0))),ABS(B$2)))"

result = Evaluate(s)



--
Regards,
Tom Ogilvy



"sdg8481" wrote in message
...
Hi,

Thanks for this but i can't seem to get it working, and i don't think it
takes into account holidays etc..... Also i also need it to calculate 13
and
8 working days in advance which obviously isn't so easy as 15. what does
the
dt represent. Is it not possible to to use the earlier formula in VBA.
Thanks

"Tom Ogilvy" wrote:

Do something like this pseudo code.

Dim cnt as Long, dt as Date
dt = DataValue("01/01/2006")
do while cnt < 15
dt = dt + 1
if weekday(dt,2) < 6 then cnt = cnt + 1
Loop

--
Regards,
Tom Ogilvy



"sdg8481" wrote in message
...
Hi,

I want to replace the following VBA code;

' guess 15-day date!
InBy_Box.Text = DateValue(Referral_Box.Text) + 21

Where 21 days are guessed from the referral date, with the following
equivlant formula;


=A2+IF(B$2=0,0,SIGN(B$2)*SMALL(IF((WEEKDAY(A2+SIGN (B$2)*(ROW(INDIRECT("1:"&ABS(B$2)*10))),2)<6)*ISNA (MATCH(A2+SIGN(B$2)*(ROW(INDIRECT("1:"&ABS(B$2)*10 ))),C$2:C$3,0)),ROW(INDIRECT("1:"&ABS(B$2)*10))),A BS(B$2)))

This Array formula is work around for the Workday function if no
analysis
tool pak can be included, however i need to know how this should be
transcribed into VBA, so that upon entering the referral date in a form
the
future date (workdays) automatically calculates. Please help i'm really
stuck
and do not know much about this line of VBA.

Thanks In Advance