| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA loops and subs
Trying to clean up some code for entering Daily sales figures from a userform
to another sheet. As the below code sits, it works fine and does everything I want except after several "months" of code VBA says it is to large to compile (Didn't know there was a limit to size). Tried to clean up the Daily checks with a DO_WHILE and FOR_NEXT loops but reference errors put my brain in an error loop. Any hints or suggestions would be greatly appreciated. Example: Private Sub cmdEnter_Click() Sheet2.Select Dim Msg, Style, Title 'Set up Message Box Msg = "Sales Already Entered for this Day. Select Another Day" ' Define message. Style = vbOKOnly ' Define buttons. Title = "Entry Error" ' Define MsgBox Title. Dim curLiq, curDraft As Currency, curBottle As Currency, curCan As Currency, curWine As Currency, curSoda As Currency curLiq = tbLiq 'Set inputs to currency for this section curDraft = tbDraft curBottle = tbBottle curCan = tbCan curWine = tbWine curSoda = tbSoda 'Start Month Checks If combMonth = "January" Then ' Check Month Selection ' Start Day Checks If combDay = "1st" Then If [c54].Value 0 Then 'Check if Sales already Entered GoSub MyResp 'If entered tell to select different day Else Range("c54").Activate ' If blank Enter Sales for day GoSub MySalesUpdate End If ElseIf combDay = "2nd" Then If [c55].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c55").Activate GoSub MySalesUpdate End If ElseIf combDay = "3rd" Then If [c56].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c56").Activate GoSub MySalesUpdate End If ElseIf combDay = "4th" Then If [c57].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c57").Activate GoSub MySalesUpdate End If ElseIf combDay = "5th" Then If [c58].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c58").Activate GoSub MySalesUpdate End If 'etc,etc,etc for the rest of the year |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA loops and subs
this is why we have loops and pass parameters
Dim thisYear As Long Dim startCell As Range Dim thisDay As Date ' thisYear = 2006 Set startCell = Range("C34") For thisDay = DateSerial(thiyear, 1, 1) To DateSerial(thisYear, 12, 31) Set target = startCell.Offset(thisDay - DateSerial(thisYear, 1, 1)) If target.Value 0 Then MyResp Else MySalesUpdate target End If Next ''''the procedure called MySalesUpdate needs one pass parameter, a range object that's passed from the loop. There's no need to actually select the cell. Also, try to avoid cell references like [A1], instead, use Range("A1") HTH "harrar" wrote: Trying to clean up some code for entering Daily sales figures from a userform to another sheet. As the below code sits, it works fine and does everything I want except after several "months" of code VBA says it is to large to compile (Didn't know there was a limit to size). Tried to clean up the Daily checks with a DO_WHILE and FOR_NEXT loops but reference errors put my brain in an error loop. Any hints or suggestions would be greatly appreciated. Example: Private Sub cmdEnter_Click() Sheet2.Select Dim Msg, Style, Title 'Set up Message Box Msg = "Sales Already Entered for this Day. Select Another Day" ' Define message. Style = vbOKOnly ' Define buttons. Title = "Entry Error" ' Define MsgBox Title. Dim curLiq, curDraft As Currency, curBottle As Currency, curCan As Currency, curWine As Currency, curSoda As Currency curLiq = tbLiq 'Set inputs to currency for this section curDraft = tbDraft curBottle = tbBottle curCan = tbCan curWine = tbWine curSoda = tbSoda 'Start Month Checks If combMonth = "January" Then ' Check Month Selection ' Start Day Checks If combDay = "1st" Then If [c54].Value 0 Then 'Check if Sales already Entered GoSub MyResp 'If entered tell to select different day Else Range("c54").Activate ' If blank Enter Sales for day GoSub MySalesUpdate End If ElseIf combDay = "2nd" Then If [c55].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c55").Activate GoSub MySalesUpdate End If ElseIf combDay = "3rd" Then If [c56].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c56").Activate GoSub MySalesUpdate End If ElseIf combDay = "4th" Then If [c57].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c57").Activate GoSub MySalesUpdate End If ElseIf combDay = "5th" Then If [c58].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c58").Activate GoSub MySalesUpdate End If 'etc,etc,etc for the rest of the year |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA loops and subs
Use this, you only need one set of code not 31
Private Sub cmdEnter_Click() Sheet2.Select Dim Msg, Style, Title 'Set up Message Box Msg = "Sales Already Entered for this Day. Select Another Day" ' Define message. Style = vbOKOnly ' Define buttons. Title = "Entry Error" ' Define MsgBox Title. Dim curLiq, curDraft As Currency, curBottle As Currency Dim curCan As Currency, curWine As Currency, curSoda As Currency Dim DayOff As Long curLiq = tbLiq 'Set inputs to currency for this section curDraft = tbDraft curBottle = tbBottle curCan = tbCan curWine = tbWine curSoda = tbSoda 'Start Month Checks If combMonth = "January" Then ' Check Month Selection DayOff = CLng(Left(combDay, Len(comboDay) - 2)) + 53 If Range("C" & DayOff).Value 0 Then 'Check if Sales already Entered GoSub MyResp 'If entered tell to select different day Else Range("C" & DayOff).Activate ' If blank Enter Sales for day GoSub MySalesUpdate End If -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "harrar" wrote in message ... Trying to clean up some code for entering Daily sales figures from a userform to another sheet. As the below code sits, it works fine and does everything I want except after several "months" of code VBA says it is to large to compile (Didn't know there was a limit to size). Tried to clean up the Daily checks with a DO_WHILE and FOR_NEXT loops but reference errors put my brain in an error loop. Any hints or suggestions would be greatly appreciated. Example: Private Sub cmdEnter_Click() Sheet2.Select Dim Msg, Style, Title 'Set up Message Box Msg = "Sales Already Entered for this Day. Select Another Day" ' Define message. Style = vbOKOnly ' Define buttons. Title = "Entry Error" ' Define MsgBox Title. Dim curLiq, curDraft As Currency, curBottle As Currency, curCan As Currency, curWine As Currency, curSoda As Currency curLiq = tbLiq 'Set inputs to currency for this section curDraft = tbDraft curBottle = tbBottle curCan = tbCan curWine = tbWine curSoda = tbSoda 'Start Month Checks If combMonth = "January" Then ' Check Month Selection ' Start Day Checks If combDay = "1st" Then If [c54].Value 0 Then 'Check if Sales already Entered GoSub MyResp 'If entered tell to select different day Else Range("c54").Activate ' If blank Enter Sales for day GoSub MySalesUpdate End If ElseIf combDay = "2nd" Then If [c55].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c55").Activate GoSub MySalesUpdate End If ElseIf combDay = "3rd" Then If [c56].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c56").Activate GoSub MySalesUpdate End If ElseIf combDay = "4th" Then If [c57].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c57").Activate GoSub MySalesUpdate End If ElseIf combDay = "5th" Then If [c58].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c58").Activate GoSub MySalesUpdate End If 'etc,etc,etc for the rest of the year |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA loops and subs
You can also move all the other subs into different modules, there is a
limit of 64K per module, so break them up. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "harrar" wrote in message ... Trying to clean up some code for entering Daily sales figures from a userform to another sheet. As the below code sits, it works fine and does everything I want except after several "months" of code VBA says it is to large to compile (Didn't know there was a limit to size). Tried to clean up the Daily checks with a DO_WHILE and FOR_NEXT loops but reference errors put my brain in an error loop. Any hints or suggestions would be greatly appreciated. Example: Private Sub cmdEnter_Click() Sheet2.Select Dim Msg, Style, Title 'Set up Message Box Msg = "Sales Already Entered for this Day. Select Another Day" ' Define message. Style = vbOKOnly ' Define buttons. Title = "Entry Error" ' Define MsgBox Title. Dim curLiq, curDraft As Currency, curBottle As Currency, curCan As Currency, curWine As Currency, curSoda As Currency curLiq = tbLiq 'Set inputs to currency for this section curDraft = tbDraft curBottle = tbBottle curCan = tbCan curWine = tbWine curSoda = tbSoda 'Start Month Checks If combMonth = "January" Then ' Check Month Selection ' Start Day Checks If combDay = "1st" Then If [c54].Value 0 Then 'Check if Sales already Entered GoSub MyResp 'If entered tell to select different day Else Range("c54").Activate ' If blank Enter Sales for day GoSub MySalesUpdate End If ElseIf combDay = "2nd" Then If [c55].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c55").Activate GoSub MySalesUpdate End If ElseIf combDay = "3rd" Then If [c56].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c56").Activate GoSub MySalesUpdate End If ElseIf combDay = "4th" Then If [c57].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c57").Activate GoSub MySalesUpdate End If ElseIf combDay = "5th" Then If [c58].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c58").Activate GoSub MySalesUpdate End If 'etc,etc,etc for the rest of the year |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA loops and subs
Thanks for your quick reponse. Unfortunetly, when debugging your code
segement, this line: Set target = startCell.Offset(thisDay - DateSerial(thisYear, 1, 1)) returned an "Application-defined or Object-defined error Also, isn't square brackets proper coding versus typing in Evaluate each time. "Patrick Molloy" wrote: this is why we have loops and pass parameters Dim thisYear As Long Dim startCell As Range Dim thisDay As Date ' thisYear = 2006 Set startCell = Range("C34") For thisDay = DateSerial(thiyear, 1, 1) To DateSerial(thisYear, 12, 31) Set target = startCell.Offset(thisDay - DateSerial(thisYear, 1, 1)) If target.Value 0 Then MyResp Else MySalesUpdate target End If Next ''''the procedure called MySalesUpdate needs one pass parameter, a range object that's passed from the loop. There's no need to actually select the cell. Also, try to avoid cell references like [A1], instead, use Range("A1") HTH "harrar" wrote: Trying to clean up some code for entering Daily sales figures from a userform to another sheet. As the below code sits, it works fine and does everything I want except after several "months" of code VBA says it is to large to compile (Didn't know there was a limit to size). Tried to clean up the Daily checks with a DO_WHILE and FOR_NEXT loops but reference errors put my brain in an error loop. Any hints or suggestions would be greatly appreciated. Example: Private Sub cmdEnter_Click() Sheet2.Select Dim Msg, Style, Title 'Set up Message Box Msg = "Sales Already Entered for this Day. Select Another Day" ' Define message. Style = vbOKOnly ' Define buttons. Title = "Entry Error" ' Define MsgBox Title. Dim curLiq, curDraft As Currency, curBottle As Currency, curCan As Currency, curWine As Currency, curSoda As Currency curLiq = tbLiq 'Set inputs to currency for this section curDraft = tbDraft curBottle = tbBottle curCan = tbCan curWine = tbWine curSoda = tbSoda 'Start Month Checks If combMonth = "January" Then ' Check Month Selection ' Start Day Checks If combDay = "1st" Then If [c54].Value 0 Then 'Check if Sales already Entered GoSub MyResp 'If entered tell to select different day Else Range("c54").Activate ' If blank Enter Sales for day GoSub MySalesUpdate End If ElseIf combDay = "2nd" Then If [c55].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c55").Activate GoSub MySalesUpdate End If ElseIf combDay = "3rd" Then If [c56].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c56").Activate GoSub MySalesUpdate End If ElseIf combDay = "4th" Then If [c57].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c57").Activate GoSub MySalesUpdate End If ElseIf combDay = "5th" Then If [c58].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c58").Activate GoSub MySalesUpdate End If 'etc,etc,etc for the rest of the year |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA loops and subs
Thanks for the quick response,Bob. When debugging the code an "Invalid
Procedure Call or Argument" error popped up on this line: DayOff = CLng(Left(combDay, Len(comboDay) - 2)) + 53 Thanks again "Bob Phillips" wrote: Use this, you only need one set of code not 31 Private Sub cmdEnter_Click() Sheet2.Select Dim Msg, Style, Title 'Set up Message Box Msg = "Sales Already Entered for this Day. Select Another Day" ' Define message. Style = vbOKOnly ' Define buttons. Title = "Entry Error" ' Define MsgBox Title. Dim curLiq, curDraft As Currency, curBottle As Currency Dim curCan As Currency, curWine As Currency, curSoda As Currency Dim DayOff As Long curLiq = tbLiq 'Set inputs to currency for this section curDraft = tbDraft curBottle = tbBottle curCan = tbCan curWine = tbWine curSoda = tbSoda 'Start Month Checks If combMonth = "January" Then ' Check Month Selection DayOff = CLng(Left(combDay, Len(comboDay) - 2)) + 53 If Range("C" & DayOff).Value 0 Then 'Check if Sales already Entered GoSub MyResp 'If entered tell to select different day Else Range("C" & DayOff).Activate ' If blank Enter Sales for day GoSub MySalesUpdate End If -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "harrar" wrote in message ... Trying to clean up some code for entering Daily sales figures from a userform to another sheet. As the below code sits, it works fine and does everything I want except after several "months" of code VBA says it is to large to compile (Didn't know there was a limit to size). Tried to clean up the Daily checks with a DO_WHILE and FOR_NEXT loops but reference errors put my brain in an error loop. Any hints or suggestions would be greatly appreciated. Example: Private Sub cmdEnter_Click() Sheet2.Select Dim Msg, Style, Title 'Set up Message Box Msg = "Sales Already Entered for this Day. Select Another Day" ' Define message. Style = vbOKOnly ' Define buttons. Title = "Entry Error" ' Define MsgBox Title. Dim curLiq, curDraft As Currency, curBottle As Currency, curCan As Currency, curWine As Currency, curSoda As Currency curLiq = tbLiq 'Set inputs to currency for this section curDraft = tbDraft curBottle = tbBottle curCan = tbCan curWine = tbWine curSoda = tbSoda 'Start Month Checks If combMonth = "January" Then ' Check Month Selection ' Start Day Checks If combDay = "1st" Then If [c54].Value 0 Then 'Check if Sales already Entered GoSub MyResp 'If entered tell to select different day Else Range("c54").Activate ' If blank Enter Sales for day GoSub MySalesUpdate End If ElseIf combDay = "2nd" Then If [c55].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c55").Activate GoSub MySalesUpdate End If ElseIf combDay = "3rd" Then If [c56].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c56").Activate GoSub MySalesUpdate End If ElseIf combDay = "4th" Then If [c57].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c57").Activate GoSub MySalesUpdate End If ElseIf combDay = "5th" Then If [c58].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c58").Activate GoSub MySalesUpdate End If 'etc,etc,etc for the rest of the year |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA loops and subs
Did you correct my typo?
DayOff = CLng(Left(comboDay, Len(comboDay) - 2)) + 53 Then, is the combo list always of the form 1st6, 2nd, 3rd, etc. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "harrar" wrote in message ... Thanks for the quick response,Bob. When debugging the code an "Invalid Procedure Call or Argument" error popped up on this line: DayOff = CLng(Left(combDay, Len(comboDay) - 2)) + 53 Thanks again "Bob Phillips" wrote: Use this, you only need one set of code not 31 Private Sub cmdEnter_Click() Sheet2.Select Dim Msg, Style, Title 'Set up Message Box Msg = "Sales Already Entered for this Day. Select Another Day" ' Define message. Style = vbOKOnly ' Define buttons. Title = "Entry Error" ' Define MsgBox Title. Dim curLiq, curDraft As Currency, curBottle As Currency Dim curCan As Currency, curWine As Currency, curSoda As Currency Dim DayOff As Long curLiq = tbLiq 'Set inputs to currency for this section curDraft = tbDraft curBottle = tbBottle curCan = tbCan curWine = tbWine curSoda = tbSoda 'Start Month Checks If combMonth = "January" Then ' Check Month Selection DayOff = CLng(Left(combDay, Len(comboDay) - 2)) + 53 If Range("C" & DayOff).Value 0 Then 'Check if Sales already Entered GoSub MyResp 'If entered tell to select different day Else Range("C" & DayOff).Activate ' If blank Enter Sales for day GoSub MySalesUpdate End If -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "harrar" wrote in message ... Trying to clean up some code for entering Daily sales figures from a userform to another sheet. As the below code sits, it works fine and does everything I want except after several "months" of code VBA says it is to large to compile (Didn't know there was a limit to size). Tried to clean up the Daily checks with a DO_WHILE and FOR_NEXT loops but reference errors put my brain in an error loop. Any hints or suggestions would be greatly appreciated. Example: Private Sub cmdEnter_Click() Sheet2.Select Dim Msg, Style, Title 'Set up Message Box Msg = "Sales Already Entered for this Day. Select Another Day" ' Define message. Style = vbOKOnly ' Define buttons. Title = "Entry Error" ' Define MsgBox Title. Dim curLiq, curDraft As Currency, curBottle As Currency, curCan As Currency, curWine As Currency, curSoda As Currency curLiq = tbLiq 'Set inputs to currency for this section curDraft = tbDraft curBottle = tbBottle curCan = tbCan curWine = tbWine curSoda = tbSoda 'Start Month Checks If combMonth = "January" Then ' Check Month Selection ' Start Day Checks If combDay = "1st" Then If [c54].Value 0 Then 'Check if Sales already Entered GoSub MyResp 'If entered tell to select different day Else Range("c54").Activate ' If blank Enter Sales for day GoSub MySalesUpdate End If ElseIf combDay = "2nd" Then If [c55].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c55").Activate GoSub MySalesUpdate End If ElseIf combDay = "3rd" Then If [c56].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c56").Activate GoSub MySalesUpdate End If ElseIf combDay = "4th" Then If [c57].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c57").Activate GoSub MySalesUpdate End If ElseIf combDay = "5th" Then If [c58].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c58").Activate GoSub MySalesUpdate End If 'etc,etc,etc for the rest of the year |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA loops and subs
Thank You Very Much, Bob
Didn't catch the typo but after correcting, the error still appeared. With a slight alteration though, I got it to work fine by adding a preceeding 0 to 1st, 2nd changing it to 01st, 02nd, etc and changing the Len(combDay) - 2 to 4 - 2)) + 53. It didn't seem to like the nested Len for some reason. Thanks again for pointing me in the right direction. Saved a lot of coding. "Bob Phillips" wrote: Did you correct my typo? DayOff = CLng(Left(comboDay, Len(comboDay) - 2)) + 53 Then, is the combo list always of the form 1st6, 2nd, 3rd, etc. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "harrar" wrote in message ... Thanks for the quick response,Bob. When debugging the code an "Invalid Procedure Call or Argument" error popped up on this line: DayOff = CLng(Left(combDay, Len(comboDay) - 2)) + 53 Thanks again "Bob Phillips" wrote: Use this, you only need one set of code not 31 Private Sub cmdEnter_Click() Sheet2.Select Dim Msg, Style, Title 'Set up Message Box Msg = "Sales Already Entered for this Day. Select Another Day" ' Define message. Style = vbOKOnly ' Define buttons. Title = "Entry Error" ' Define MsgBox Title. Dim curLiq, curDraft As Currency, curBottle As Currency Dim curCan As Currency, curWine As Currency, curSoda As Currency Dim DayOff As Long curLiq = tbLiq 'Set inputs to currency for this section curDraft = tbDraft curBottle = tbBottle curCan = tbCan curWine = tbWine curSoda = tbSoda 'Start Month Checks If combMonth = "January" Then ' Check Month Selection DayOff = CLng(Left(combDay, Len(comboDay) - 2)) + 53 If Range("C" & DayOff).Value 0 Then 'Check if Sales already Entered GoSub MyResp 'If entered tell to select different day Else Range("C" & DayOff).Activate ' If blank Enter Sales for day GoSub MySalesUpdate End If -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "harrar" wrote in message ... Trying to clean up some code for entering Daily sales figures from a userform to another sheet. As the below code sits, it works fine and does everything I want except after several "months" of code VBA says it is to large to compile (Didn't know there was a limit to size). Tried to clean up the Daily checks with a DO_WHILE and FOR_NEXT loops but reference errors put my brain in an error loop. Any hints or suggestions would be greatly appreciated. Example: Private Sub cmdEnter_Click() Sheet2.Select Dim Msg, Style, Title 'Set up Message Box Msg = "Sales Already Entered for this Day. Select Another Day" ' Define message. Style = vbOKOnly ' Define buttons. Title = "Entry Error" ' Define MsgBox Title. Dim curLiq, curDraft As Currency, curBottle As Currency, curCan As Currency, curWine As Currency, curSoda As Currency curLiq = tbLiq 'Set inputs to currency for this section curDraft = tbDraft curBottle = tbBottle curCan = tbCan curWine = tbWine curSoda = tbSoda 'Start Month Checks If combMonth = "January" Then ' Check Month Selection ' Start Day Checks If combDay = "1st" Then If [c54].Value 0 Then 'Check if Sales already Entered GoSub MyResp 'If entered tell to select different day Else Range("c54").Activate ' If blank Enter Sales for day GoSub MySalesUpdate End If ElseIf combDay = "2nd" Then If [c55].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c55").Activate GoSub MySalesUpdate End If ElseIf combDay = "3rd" Then If [c56].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c56").Activate GoSub MySalesUpdate End If ElseIf combDay = "4th" Then If [c57].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c57").Activate GoSub MySalesUpdate End If ElseIf combDay = "5th" Then If [c58].Value 0 Then 'Check if Data already Entered GoSub MyResp Else Range("c58").Activate GoSub MySalesUpdate End If 'etc,etc,etc for the rest of the year |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA loops and subs
Didn't I do well <VBG
Glad u sorted it. Bob "harrar" wrote in message ... Thank You Very Much, Bob Didn't catch the typo but after correcting, the error still appeared. With a slight alteration though, I got it to work fine by adding a preceeding 0 to 1st, 2nd changing it to 01st, 02nd, etc and changing the Len(combDay) - 2 to 4 - 2)) + 53. It didn't seem to like the nested Len for some reason. Thanks again for pointing me in the right direction. Saved a lot of coding. "Bob Phillips" wrote: Did you correct my typo? DayOff = CLng(Left(comboDay, Len(comboDay) - 2)) + 53 Then, is the combo list always of the form 1st6, 2nd, 3rd, etc. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Combining two Subs | Excel Discussion (Misc queries) | |||
| Scope of Subs? | Excel Programming | |||
| ending subs | Excel Programming | |||
| Private subs | Excel Programming | |||
| Stringing Subs | Excel Programming | |||
Linear Mode
