Array formula in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
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)
 
Back
Top