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))) "

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))) "

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))) "

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))) "

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))) "

You can't refer to a control on a UserForm directly in a formula.
Try something like

Dim S As String
S = "=" & Referral_Box.Text &
"+IF(13=0,0,SIGN(13)*SMALL(IF((WEEKDAY(" & _
Referral_Box.Text &
"+SIGN(13)*(ROW(INDIRECT(""1:""&ABS(13)*10))),2)<6 )*ISNA(MATCH("
& _
Referral_Box.Text &
"+SIGN(13)*(ROW(INDIRECT(""1:""&ABS(13)*10))), 25/12/06,0)),ROW(INDIRECT(""1:""&ABS(13)*10))),ABS(13))) "
Debug.Print Evaluate(S)



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



"sdg8481" wrote in message
...
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))) "

Hi, again thanks for your help much appricated, however i've tried the
following and i just get a return of FALSE, sorry to be a pain but if you
haven't gathered i'm a novice to VBA.

' guess 13 day date!
Dim S As String
InBy_Box = S = "Referral_Box" & S &
"+IF(13=0,0,SIGN(13)*SMALL(IF((WEEKDAY(" & S &
"+SIGN(13)*(ROW(INDIRECT(""1:""&ABS(13)*10))),2)<6 )*ISNA(MATCH(" & S &
"+SIGN(13)*(ROW(INDIRECT(""1:""&ABS(13)*10))), 25/12/06,0)),ROW(INDIRECT(""1:""&ABS(13)*10))),ABS(13))) "
Debug.Print Evaluate(S)


"Chip Pearson" wrote:

You can't refer to a control on a UserForm directly in a formula.
Try something like

Dim S As String
S = "=" & Referral_Box.Text &
"+IF(13=0,0,SIGN(13)*SMALL(IF((WEEKDAY(" & _
Referral_Box.Text &
"+SIGN(13)*(ROW(INDIRECT(""1:""&ABS(13)*10))),2)<6 )*ISNA(MATCH("
& _
Referral_Box.Text &
"+SIGN(13)*(ROW(INDIRECT(""1:""&ABS(13)*10))), 25/12/06,0)),ROW(INDIRECT(""1:""&ABS(13)*10))),ABS(13))) "
Debug.Print Evaluate(S)



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



"sdg8481" wrote in message
...
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))) "

If you're getting a result of FALSE, that means you have an IF
statement lacking an 'else' result. Examine your formula closely
to determine which IF doesn't have and 'else'.


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


"sdg8481" wrote in message
...
Hi, again thanks for your help much appricated, however i've
tried the
following and i just get a return of FALSE, sorry to be a pain
but if you
haven't gathered i'm a novice to VBA.

' guess 13 day date!
Dim S As String
InBy_Box = S = "Referral_Box" & S &
"+IF(13=0,0,SIGN(13)*SMALL(IF((WEEKDAY(" & S &
"+SIGN(13)*(ROW(INDIRECT(""1:""&ABS(13)*10))),2)<6 )*ISNA(MATCH("
& S &
"+SIGN(13)*(ROW(INDIRECT(""1:""&ABS(13)*10))), 25/12/06,0)),ROW(INDIRECT(""1:""&ABS(13)*10))),ABS(13))) "
Debug.Print Evaluate(S)


"Chip Pearson" wrote:

You can't refer to a control on a UserForm directly in a
formula.
Try something like

Dim S As String
S = "=" & Referral_Box.Text &
"+IF(13=0,0,SIGN(13)*SMALL(IF((WEEKDAY(" & _
Referral_Box.Text &
"+SIGN(13)*(ROW(INDIRECT(""1:""&ABS(13)*10))),2)<6 )*ISNA(MATCH("
& _
Referral_Box.Text &
"+SIGN(13)*(ROW(INDIRECT(""1:""&ABS(13)*10))), 25/12/06,0)),ROW(INDIRECT(""1:""&ABS(13)*10))),ABS(13))) "
Debug.Print Evaluate(S)



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



"sdg8481" wrote in message
...
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))) "

Assuming your formula is correct otherwise,

Dim S As String
S = InBy_Box.Text
S = S &
"+IF(13=0,0,SIGN(13)*SMALL(IF((WEEKDAY(" & S &
"+SIGN(13)*(ROW(INDIRECT(""1:""&ABS(13)*10))),2)<6 )*ISNA(MATCH(" & S &
"+SIGN(13)*(ROW(INDIRECT(""1:""&ABS(13)*10))), 25/12/06,0)),ROW(INDIRECT(""1:""&ABS(13)*10))),ABS(13))) "
Debug.Print Evaluate(S)

--
Regards,
Tom Ogilvy


"sdg8481" wrote in message
...
Hi, again thanks for your help much appricated, however i've tried the
following and i just get a return of FALSE, sorry to be a pain but if you
haven't gathered i'm a novice to VBA.

' guess 13 day date!
Dim S As String
InBy_Box = S = "Referral_Box" & S &
"+IF(13=0,0,SIGN(13)*SMALL(IF((WEEKDAY(" & S &
"+SIGN(13)*(ROW(INDIRECT(""1:""&ABS(13)*10))),2)<6 )*ISNA(MATCH(" & S &
"+SIGN(13)*(ROW(INDIRECT(""1:""&ABS(13)*10))), 25/12/06,0)),ROW(INDIRECT(""1:""&ABS(13)*10))),ABS(13))) "
Debug.Print Evaluate(S)


"Chip Pearson" wrote:

You can't refer to a control on a UserForm directly in a formula.
Try something like

Dim S As String
S = "=" & Referral_Box.Text &
"+IF(13=0,0,SIGN(13)*SMALL(IF((WEEKDAY(" & _
Referral_Box.Text &
"+SIGN(13)*(ROW(INDIRECT(""1:""&ABS(13)*10))),2)<6 )*ISNA(MATCH("
& _
Referral_Box.Text &
"+SIGN(13)*(ROW(INDIRECT(""1:""&ABS(13)*10))), 25/12/06,0)),ROW(INDIRECT(""1:""&ABS(13)*10))),ABS(13))) "
Debug.Print Evaluate(S)



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



"sdg8481" wrote in message
...
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))) "