| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| CODING ERRor | Excel Discussion (Misc queries) | |||
| Help with coding error | Excel Programming | |||
| Error in VBA Coding in Macro | Excel Programming | |||
| coding error | Excel Programming | |||
| Error resulting from coding | Excel Programming | |||
Linear Mode
