VBA loops and subs

G

Guest

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
 
G

Guest

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
 
B

Bob Phillips

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 said:
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
Click to expand...
 
B

Bob Phillips

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)
 
G

Guest

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 said:
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 said:
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
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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
Click to expand...
Click to expand...
 
B

Bob Phillips

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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)

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
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Phillips

Didn't I do well <VBG>

Glad u sorted it.

Bob

harrar said:
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.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

AutoDetect the path of the file? 4
non-expanding range, and also a VBA Sort error 7
FWT Newsletter - Weekly - October 11, 2004 0
FWT Newsletter - Weekly - November 1, 2004 0
FWT Newsletter - Weekly - September 6, 2004 1

Top