Array formula in VBA

[Guest]

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))),ABS(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

 

[Tom Ogilvy]

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

 

[Guest]

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]

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)*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))),ABS(B$2)))"

result = Evaluate(s)