| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
|
|
|
||
|
|
| |
|
Guest
Posts: n/a
|
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 -- Gary Keramidas Excel 2003 "ecce_ego" <(E-Mail Removed)> wrote in message news  DDAD14D-C9DC-4A4B-BA0A-(E-Mail Removed)...>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! |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news DDAD14D-C9DC-4A4B-BA0A-(E-Mail Removed)...> 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! |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:#(E-Mail Removed)... > 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" <(E-Mail Removed)> wrote in message > news DDAD14D-C9DC-4A4B-BA0A-(E-Mail Removed)...>> 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! > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
> 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" <(E-Mail Removed)> wrote in message news:#(E-Mail Removed)... > 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" <(E-Mail Removed)> wrote in message > news:#(E-Mail Removed)... >> 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" <(E-Mail Removed)> wrote in message >> news DDAD14D-C9DC-4A4B-BA0A-(E-Mail Removed)...>>> 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! >> |
|
|
|
|
|||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| have window vista basic.understand basic don't have fax & scan | jay sureka | Windows Vista Print / Fax / Scan | 4 | 7th Jan 2008 07:02 PM |
| Recordset looping (and debug looping!) | =?Utf-8?B?U3RlcGhhbmll?= | Microsoft Access Form Coding | 16 | 7th May 2007 08:17 PM |
| Looping Sound file with Looping slide show? | =?Utf-8?B?a29rYW5lZWdvbGQ=?= | Microsoft Powerpoint | 11 | 19th Jan 2005 06:14 PM |
| Problem with basic looping and criteria | =?Utf-8?B?SmVmZg==?= | Microsoft Excel Programming | 2 | 29th Dec 2004 10:05 PM |
| Basic Looping Question | =?Utf-8?B?SmVubg==?= | Microsoft Excel Misc | 3 | 21st Jul 2004 02:33 PM |
