Basic Looping

[ecce_ego]

I have made a form that will enter customer data in a worksheet and calculate
several things, including checking a date range and adding totals to a table
of weekly totals for the month. The code I wrote works, but I am new to
programming and do not know how to create a more logically structured program.

Here is a portion of the code I've written; how do I use a loop to
accomplish the same thing?

---------------------

If aMonth = "1" Then
If Week = "1" Then
WeekTotalJan = 0
Range("K4").Select
WeekTotalJan = AmountDueTextBox.Value
ActiveCell.Value = ActiveCell.Value + WeekTotalJan
Else
If Week = "2" Then
WeekTotalJan = 0
Range("K5").Select
WeekTotalJan = AmountDueTextBox.Value
ActiveCell.Value = ActiveCell.Value + WeekTotalJan
Else
If Week = "3" Then
WeekTotalJan = 0
Range("K6").Select
WeekTotalJan = AmountDueTextBox.Value
ActiveCell.Value = ActiveCell.Value + WeekTotalJan
Else
If Week = "4" Then
WeekTotalJan = 0
Range("K7").Select
WeekTotalJan = AmountDueTextBox.Value
ActiveCell.Value = ActiveCell.Value + WeekTotalJan
Else
If Week = "5" Then
WeekTotalJan = 0
Range("K8").Select
WeekTotalJan = AmountDueTextBox.Value
ActiveCell.Value = ActiveCell.Value + WeekTotalJan
End If
End If
End If
End If
End If
Else
If aMonth = "2" Then
If Week = "1" Then
WeekTotalFeb = 0
Range("N4").Select
WeekTotalFeb = AmountDueTextBox.Value
ActiveCell.Value = ActiveCell.Value + WeekTotalFeb
Else
If Week = "2" Then..........etc....

 

[Gary Keramidas]

couldn't really test, not sure what values you are using, but maybe this
will give you an idea


If aMonth = "1" Then
With Worksheets("Sheet1")
Select Case week
Case Is = "1"
.Range("K4").Value = .Range("K4").Value + _
AmountDueTextBox.Value
Case Is = "2"
WeekTotalJan = 0
.Range("K5").Value = .Range("K5").Value + _
AmountDueTextBox.Value
' and so on
End Select
End With
End If

 

[Rick Rothstein]

If I have interpreted your code correctly, these four lines of code should
replace ALL the code you posted (including that parts that would be covered
by the etc.)...

WeekTotalJan = 0
Cells(3 + Week, "K").Offset(0, 3 * (aMonth - 1)).Select
WeekTotalJan = AmountDueTextBox.Value
ActiveCell.Value = ActiveCell.Value + WeekTotalJan

 

[Rick Rothstein]

Whoops! I missed the switch over between WeekTotalJan to WeekTotalFeb when
aMonth changed. The BEST way to handle this would be to convert your
individual WeekTotalJan, WeekTotalFeb, WeekTotalMar, etc. to an array. This
would require you to change other parts of your code where you use these
variables, but using arrays makes constructions such as this so much more
streamlined. So, wherever you have Dim'med the variables WeekTotalJan,
WeekTotalFeb, WeekTotalMar, etc., delete them and replace them with this...

Dim WeekTotal(1 To 12) As Long

Now, wherever in your code you have WeekTotalJan, replace it with
WeekTotal(1) and wherever in your code you have WeekTotalFeb, replace it
with WeekTotal(2), and wherever in your code you have WeekTotalMar, replace
it with WeekTotal(3), etc. (you can use Edit/Replace from the VB editor's
menu bar to make all the replacements... make sure to use the "Current
Project" option). Now, once you have done that, here is the modification to
the four lines of code I posted previously....

WeekTotal(aMonth) = 0
Cells(3 + Week, "K").Offset(0, 3 * (aMonth - 1)).Select
WeekTotalJan = AmountDueTextBox.Value
ActiveCell.Value = ActiveCell.Value + WeekTotalJan

--
Rick (MVP - Excel)

If I have interpreted your code correctly, these four lines of code should
replace ALL the code you posted (including that parts that would be
covered by the etc.)...

WeekTotalJan = 0
Cells(3 + Week, "K").Offset(0, 3 * (aMonth - 1)).Select
WeekTotalJan = AmountDueTextBox.Value
ActiveCell.Value = ActiveCell.Value + WeekTotalJan

 

[Rick Rothstein]

WeekTotal(aMonth) = 0

Cells(3 + Week, "K").Offset(0, 3 * (aMonth - 1)).Select
WeekTotalJan = AmountDueTextBox.Value
ActiveCell.Value = ActiveCell.Value + WeekTotalJan

CORRECTION... the above has errors. I just noticed you set the WeekTotalJan,
WeekTotalFeb, etc. variable twice, once to 0 (which was totally unnecessary
because later on) you set it to AmountDueTextBox.Value which overrides your
original assignment of 0 to them. So, my four line replacement becomes this
three line replacement instead...

Cells(3 + Week, "K").Offset(0, 3 * (aMonth - 1)).Select
WeekTotal(aMonth) = AmountDueTextBox.Value
ActiveCell.Value = ActiveCell.Value + WeekTotal(aMonth)

Sorry about any confusion my multiple posts may have caused you. To
summarize, use the array structure I outlined in my last posting, but use
the above three lines of code to replace all your originally posted code.

--
Rick (MVP - Excel)

Whoops! I missed the switch over between WeekTotalJan to WeekTotalFeb when
aMonth changed. The BEST way to handle this would be to convert your
individual WeekTotalJan, WeekTotalFeb, WeekTotalMar, etc. to an array.
This would require you to change other parts of your code where you use
these variables, but using arrays makes constructions such as this so much
more streamlined. So, wherever you have Dim'med the variables
WeekTotalJan, WeekTotalFeb, WeekTotalMar, etc., delete them and replace
them with this...

Dim WeekTotal(1 To 12) As Long

Now, wherever in your code you have WeekTotalJan, replace it with
WeekTotal(1) and wherever in your code you have WeekTotalFeb, replace it
with WeekTotal(2), and wherever in your code you have WeekTotalMar,
replace it with WeekTotal(3), etc. (you can use Edit/Replace from the VB
editor's menu bar to make all the replacements... make sure to use the
"Current Project" option). Now, once you have done that, here is the
modification to the four lines of code I posted previously....

WeekTotal(aMonth) = 0
Cells(3 + Week, "K").Offset(0, 3 * (aMonth - 1)).Select
WeekTotalJan = AmountDueTextBox.Value
ActiveCell.Value = ActiveCell.Value + WeekTotalJan