VBA Coding Error

[sdg8481]

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

 

[Tom Ogilvy]

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)

 

[sdg8481]

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]

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

 

[sdg8481]

the one that works ina a spreadsheet is the following;

=start_date+IF(days=0,0,SIGN(days)*SMALL(IF((WEEKDAY(start_date+SIGN(days)*(ROW(INDIRECT("1:"&ABS(days)*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.

 

[Bob Phillips]

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)

 

[Bob Phillips]

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)

 

[sdg8481]

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

 

[Tom Ogilvy]

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

 

[Chip Pearson]

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]

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.

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

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.