Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totals
I'm trying write a Macro that will total premium values for a porfolio of companies. The number of companies will vary from portfolio to portfolio. Here's what I have so far: Sub Totals() Dim Start As String Dim EndPart As String Dim Spacer As Integer Dim SumString1 As String Dim SumString2 As String Dim SumString3 As String Dim Argument As Integer Dim Counter As Integer Dim Num As Integer Spacer = 4 Start = "R[-" EndPart = "]C" Argument = Spacer Num = InputBox("How many portfolio companies are there? ") SumString1 = "=R[-4]C" For Counter = 2 To Num Argument = Counter * Spacer SumString2 = "+" & Start & Argument & EndPart SumString3 = SumString1 & SumString2 Next Counter MsgBox ("He " & SumString3) End Sub As you can see my sum only captures the first and last values, and I can't seem to figure out a way to incorporate all the values in the sume. Any help would be greatly appreciated. Thanks, Peter -- PGalla06 ------------------------------------------------------------------------ PGalla06's Profile: http://www.excelforum.com/member.php...o&userid=24260 View this thread: http://www.excelforum.com/showthread...hreadid=381238 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totals
Here's a simplified version of your code. You can use one SumString and just add it to itself repeatedly. Code: -------------------- Sub Totals() Dim Start As String Dim EndPart As String Dim Spacer As Integer Dim SumString As String Dim Argument As Integer Dim Counter As Integer Dim Num As Integer Spacer = 4 Start = "R[-" EndPart = "]C" Argument = Spacer Num = InputBox("How many portfolio companies are there? ") SumString = "=R[-4]C" For Counter = 2 To Num Argument = Counter * Spacer SumString = SumString & "+" & Start & Argument & EndPart Next Counter MsgBox ("He " & SumString) End Sub -------------------- HTH -- bhofsetz ------------------------------------------------------------------------ bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807 View this thread: http://www.excelforum.com/showthread...hreadid=381238 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totals
bhofsetz, You're awesome! Thanks, Peter -- PGalla06 ------------------------------------------------------------------------ PGalla06's Profile: http://www.excelforum.com/member.php...o&userid=24260 View this thread: http://www.excelforum.com/showthread...hreadid=381238 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Totals: Group totals different from Grand totals | Excel Discussion (Misc queries) | |||
how to enter totals and sub totals from receipts into excel. | New Users to Excel | |||
Summing Weekly Totals into Monthly Totals | Excel Worksheet Functions | |||
How do I sum YTD totals based on monthly totals | Excel Discussion (Misc queries) | |||
Comparing/matching totals in a column to totals in a row | Excel Worksheet Functions |