VBA Coding Error
 

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

VBA Coding Error
 

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

VBA Coding Error
 

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

VBA Coding Error
 

You wrote it. Get it working in a worksheet with similar values. then use
that formula in your code in the same way.

--
Regards,
Tom Ogilvy


"sdg8481" wrote:

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

VBA Coding Error
 

the one that works ina a spreadsheet is the following;

=start_date+IF(days=0,0,SIGN(days)*SMALL(IF((WEEKD AY(start_date+SIGN(days)*(ROW(INDIRECT("1:"&ABS(da ys)*10))),2)<6)*ISNA(MATCH(start_date+SIGN(days)*( ROW(INDIRECT("1:"&ABS(days)*10))),holidays,0)),ROW (INDIRECT("1:"&ABS(days)*10))),ABS(days)))

whereby i want;

Start date = Referral_Box / days = 13 / holidays = 25/12/2006

Its getting it into VBA and a form that i simply can't do, and its driving
me up the wall because i feel i'm so close but just not quite there.




"Tom Ogilvy" wrote:

You wrote it. Get it working in a worksheet with similar values. then use
that formula in your code in the same way.

--
Regards,
Tom Ogilvy


"sdg8481" wrote:

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

VBA Coding Error
 

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

VBA Coding Error
 

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

VBA Coding Error
 

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

VBA Coding Error
 

that line has been corrected several times, but you continue to post it all
screwed up.

InBy_Box = S = S

is definitely screwed up.

InBy_Bos = S = S & "anything"

will always evaluate to false

--
Regards,
Tom Ogilvy



"sdg8481" wrote:

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

VBA Coding Error
 

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

VBA Coding Error
 

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

VBA Coding Error
 

I would suggest a UDF here, such as this one written by Ron Rosenfeld

Function WD(StartDate As Date, _
ByVal NumDays As Long, _
Optional Holidays As Range = Nothing) As Date
'Workday function without Analysis Toolpak
Dim i As Long
Dim TempDate As Date
Dim c As Range
Dim Stp As Integer

Stp = Abs(NumDays) / NumDays
TempDate = StartDate
For i = Stp To NumDays Step Stp
TempDate = TempDate + Stp
If Weekday(TempDate) = vbSunday Then _
TempDate = TempDate + Stp + (Stp < 0)

If Not Holidays Is Nothing Then
Do Until Not IsError(Application.Match(CDbl(TempDate), Holidays,
0)) = False
If IsError(Application.Match(CDbl(TempDate), Holidays, 0)) =
False Then
TempDate = TempDate + Stp
If Weekday(TempDate) = vbSunday Then _
TempDate = TempDate + Stp + (Stp < 0)
End If
Loop
End If
Next i
WD = TempDate
End Function


and use like so

Debug.Print WD(CDate(Referall_Box.Text), 13, Range("AA1:AA10"))


--
HTH

Bob Phillips

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

"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(IND
IRECT("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

VBA Coding Error
 

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