| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Basic Looping
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 (MVP - Excel) "ecce_ego" wrote in message ... 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.... -------------------------- It's really a pain to write all those ifs! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Basic Looping
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) "Rick Rothstein" wrote in message ... 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 (MVP - Excel) "ecce_ego" wrote in message ... 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.... -------------------------- It's really a pain to write all those ifs! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Basic Looping
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) "Rick Rothstein" wrote in message ... 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) "Rick Rothstein" wrote in message ... 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 (MVP - Excel) "ecce_ego" wrote in message ... 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.... -------------------------- It's really a pain to write all those ifs! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Looping | Excel Discussion (Misc queries) | |||
| Looping...but why? | Excel Programming | |||
| Can I run Visual Basic procedure using Excel Visual Basic editor? | Excel Programming | |||
| Problem with basic looping and criteria | Excel Programming | |||
| Looping | Excel Programming | |||
Linear Mode
