| 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 |
| 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 | |||
Hybrid Mode
