Sub ABC()
s = "1/4/2006"
days = 13
s2 = "(Row(1:" & days * 10 & "))"
s3 = "Row(1:" & days * 10 & ")"
s1 = "DateValue(""" & s & """)+IF(13=0,0,SIGN(13)*SMALL" & _
"(IF((WEEKDAY(Datevalue(""" & s & _
""")+SIGN(13)*" & s2 & ",2)<6)*ISNA(MATCH(DateValue(""" & s & _
""")+SIGN(13)*" & s2 & "," & Range("AA1:AA10").Address & _
",0))," & s3 & "),ABS(13)))"
Debug.Print Evaluate(s1)
End Sub

worked for me.

--
Regards,
Tom Ogilvy


"sdg8481" wrote in message
...
Thankyou all very much for your help and patience, however it still don't
work. The following formula

' guess 13 day date!
Dim S As String
S = Referral_Box.Text
InBy_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))), " &
Range("AA1:AA10").Address &
",0)),ROW(INDIRECT(""1:""&ABS(13)*10))),ABS(13 )))"
Debug.Print ActiveSheet.Evaluate(S)

just returns the text and not a formula, when a referral date of
01/04/2006
is entered it returns

01/04/2006+IF(13=0,0,SIGN(13)*SMALL(IF((WEEKDAY(01/04/2006+SIGN(13)*(ROW(INDIRECT("1:"&ABS(13)*10))),2)< 6)*ISNA(MATCH(01/04/2006+SIGN(13)*(ROW(INDIRECT("1:"&ABS(13)*10))),$AA $1:$AA$10,0)),ROW(INDIRECT("1:"&ABS(13)*10))),ABS( 13)))

I think i'm going to give up if this there are no more suggestions,
because
i've taken up enough of your time. However, if you do have any quick
solutions let me know. And thanks again.


"Chip Pearson" wrote:

You're getting a False because you're comparing the string S to
the string S & "rest of your stuff".

InBy_Box = S = S & "rest of your stuff"

is the same as

If S = S & "rest of your stuff" Then
InBy_Box = True
Else
InBy_Box = False
End If

As long as "rest of your stuff" has any text at all, the result
will be false.

Change your code to

InBy_Box = S & "rest of your stuff"


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


"sdg8481" wrote in message
...
Thanks again, however i've tried the following and its still
coming up as
"False"

' guess 13 day date!
Dim S As String
S = Referral_Box.Text
InBy_Box = 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))), " &
Range("M1:M10").Address &
",0)),ROW(INDIRECT(""1:""&ABS(13)*10))),ABS(13 )))"
Debug.Print ActiveSheet.Evaluate(S)

Exit Sub

Please help, i'm getting desperate here and the PC's about to
go out the
window. Thanks

"Bob Phillips" wrote:

I see that hard-coded date was referring to holidays, so you
need to use the
holidays list range, something like

& Range("M1:M10").Address &

instead of

& Referral_Box.Text &

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"Bob Phillips" wrote in message
...
Maybe you shouldn't hard code the date

Dim S As String
S = Referral_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))), " &
Referral_Box.Text &
",0)),ROW(INDIRECT(""1:""&ABS(13)*10))),ABS(13 )))"
Debug.Print ActiveSheet.Evaluate(S)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"sdg8481" wrote in
message
...
Hi, thats what i mean how do you know if the formula is
correct, i can't
see
whats wrong with it, now i've tried it and it's coming up
blank. sorry
to
be
a pain

"Tom Ogilvy" wrote:

Assuming your formula is correct otherwise,

Dim S As String
S = Referral_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)

'InBy_Box.Text = Evaluate(S)

--
Regards,
Tom Ogilvy



"sdg8481" wrote:

just Hi,

I have the following code in VBA which should hopeful
should add 13
working
days to a date entered into a form text box
(Referral_Box). However,
i
can't
get it working as its just returning FALSE, any ideas
what wrong
thanks.

Dim S As String
S = Referral_Box.Text
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)

Whereby, InBy_Box is where i want 13 days (excluding
holidays) to be
added
to the date put in the Referral_Box field.

Thanks