VBA FormualArray error

[sdg8481]

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((WEEKDAY(Referral_Box+SIGN(13)*(ROW(INDIRECT(""1:""&ABS(13)*10))),2)<6)*ISNA(MATCH(Referral_Box+SIGN(13)*(ROW(INDIRECT(""1:""&ABS(13)*10))),25/12/06,0)),ROW(INDIRECT(""1:""&ABS(13)*10))),ABS(13)))"

 

[Chip Pearson]

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(IF((WEEKDAY(Referral_Box+SIGN(13)*(ROW(INDIRECT(""1:""&ABS(13)*10))),2)<6)*ISNA(MATCH(Referral_Box+SIGN(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]

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

 

[Chip Pearson]

What is Referral_Box? A named cell? Something else?


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

 

[sdg8481]

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

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]

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]

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]

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

 

[Tom Ogilvy]

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)