Its a text box on a form, where you input the date which needs 13 working
days adding.

existing VBA is as follows, which i can't quite get to work

' auto-calculation of 13-days date: this only happens
' when a new allocation is being made.
Private Sub Referral_Box_Exit(ByVal Cancel As MSForms.ReturnBoolean)

On Error GoTo ErrorHandler

' don't guess the 13-day date for an amendment
If Worksheets(1).amend Then Exit Sub

' guess 13 day date!
InBy_Box.Text =
Evaluate("=Referral_Box.Text+IF(13=0,0,SIGN(13)*SM ALL(IF((WEEKDAY(Referral_Box.Text+SIGN(13)*(ROW(IN DIRECT(""1:""&ABS(13)*10))),2)<6)*ISNA(MATCH(Refer ral_Box.Text+SIGN(13)*(ROW(INDIRECT(""1:""&ABS(13) *10))),25/12/06,0)),ROW(INDIRECT(""1:""&ABS(13)*10))),ABS(13))) ")

Exit Sub

ErrorHandler:

Cancel = True
MsgBox Prompt:="Please enter a valid referral date.", title:="Invalid
referral date"

End Sub

Thanks for your help

"Chip Pearson" wrote:

What is Referral_Box? A named cell? Something else?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"sdg8481" wrote in message
...
Thanks, but now i get the following message "Please enter a
valid referral
date", any ideas

"Chip Pearson" wrote:

You use FormulaArray to assign an array formula to a cell.
Perhaps you need something like

InBy_Box.Text =
Evaluate("=Referral_Box+IF(13=0,0,SIGN(13)*SMALL(I F((WEEKDAY(Referral_Box+SIGN(13)*(ROW(INDIRECT(""1 :""&ABS(13)*10))),2)<6)*ISNA(MATCH(Referral_Box+SI GN(13)*(ROW(INDIRECT(""1:""&ABS(13)*10))),25/12/06,0)),ROW(INDIRECT(""1:""&ABS(13)*10))),ABS(13))) ")



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"sdg8481" wrote in message
...
Hi, I have the following code in VBA, whereby the following
Array formula is
used to create a day 13 workdays in advance into the
InBy_Box
field on a
form. However, i can't seem to get it working any ideas
whats
wrong and how
to fix it.

Thanks

InBy_Box.Text = DataValue.FormulaArray =
"=Referral_Box+IF(13=0,0,SIGN(13)*SMALL(IF((WEEKDA Y(Referral_Box+SIGN(13)*(ROW(INDIRECT(""1:""&ABS(1 3)*10))),2)<6)*ISNA(MATCH(Referral_Box+SIGN(13)*(R OW(INDIRECT(""1:""&ABS(13)*10))),25/12/06,0)),ROW(INDIRECT(""1:""&ABS(13)*10))),ABS(13))) "