Posted to microsoft.public.excel.programming
|
|
VBA FormualArray error
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))) "
|