help with sum macro - different number of rows

I am trying to record a macro that would sum a column at the end of the
column.  However the number of rows will not always be the same.  How
do I get the sum to always show up at the end of the column.

TIA
Mark

0
malewine (1)
9/1/2006 2:53:20 PM
excel 39879 articles. 2 followers. Follow

2 Replies
438 Views

Similar Articles

[PageSpeed] 5

dim NextRow as long
with activesheet
   Nextrow = .cells(.rows.count,"A").end(xlup).row + 1
   .cells(nextrow,"A").formulaR1C1 = "=sum(r1c:r[-1]c)"
end with

This will sum from:  R1C    (R1 of the same Column) 
                to:  R[-1]C (one row up of the same column)

malewine@houston.rr.com wrote:
> 
> I am trying to record a macro that would sum a column at the end of the
> column.  However the number of rows will not always be the same.  How
> do I get the sum to always show up at the end of the column.
> 
> TIA
> Mark

-- 

Dave Peterson
0
petersod (12005)
9/1/2006 3:28:18 PM
This will select all the rows of a particular coulmn, in this case the
starting cell is "A3"

    Range("A3").Select
    Range(Selection, Selection.End(xlDown)).Select

Just do a sum on this selection and output it to a cell. Hope this
helps.

0
9/2/2006 5:52:11 AM
Reply:

Similar Artilces:

referencing to worksheet names in macro for each new worksheet inserted
Hi I created a code to insert new worksheets and rename them according t values on the new worksheet itself. Say in Cell D1, i have th worksheet name. My question is when i want to refer to this worksheet in subsequen coding, how should i code it? For eg, How should i write the ???? for Sheets("????").select? Would creatin the a variable to store the names help? Thanks in advance Ken -- Message posted from http://www.ExcelForum.com After inserting your new worksheet set it's name equal to a variable. For example SHEETS.ADD VWORKSHEET = ACTIVESHEET.NAME This method ...

Cells print so small I cannot read numbers. How do I fix?
I have been working with page break. Now I have the grid on 1 page..but it is far to small to read. now when I try to spread it back to 2 pages, it just takes the same tiny microscopic type and spreads it into 2 pages. I am stuck printing tiny type. How can I get the grid cells back to a size that is readable. It sound like you have selected Fit to 1 page in File > Page setup > Page > Scaling. Either select to fit it to 2 pages or select Adjust to 100% size -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Repl...

Deleting rows to resize workspace
Hi. I'm using a workbook that someone else created from a download. It has 23,600 rows -- yet the vertical scroll bar.. when on the last row.. makes it appear that it's only 10% from the top. In the past I've deleted all of the rows - going down to the full million - and then gone back to A1 and hit "save". Usually that refreshes the space. It's not working. I can delete them. And go to A1 and save - even tried resaving with another name.. but it (Excel) still thinks it's bigger than it is. Any ideas? Usually that technique works. Debra D...

Formula Help (to many expresions)
Could one of you give me a hand with this... I'm trying to put a formula in a spreadsheet that has too many expressions in it. I understand there is a limit to the number of equations that can be in a formula but there must be a way around the cap. Or maybe another way to write the formula? What I am trying to say in the formula is that if... If X is less than 09 then B1 = what's in cell C2 If X is less than 25 then B1 = what's in cell C3 If X is less than 51 then B1 = what's in cell C4 The expression I have written looks like this... =IF(X<10,"N/A",IF(X<...

VB code for Macro
I have set up a rule on my InBox to check for specific words and move emails to my Work folder. Now I review emials in my Work folder and drag and drop them into 1 of 4 folders based on a number 1-4. After clicking on the folder, I need to perform the following on each of the four folders: Click on first email in the folder Clt+A (to select all the emails in the folder) Ctl+C (to copy) Drag the selections to a folder name HH Click #_Button (customized button set to send an email) Ctl+v (to paste the contents in the body of the email) Click Send Steps without the comments: Click folde...

Linq, aggregate Sum problem
Hi, I figured out that I am unable to query for an aggregate function (Sum) over a column I have properly filled. The following NUnit test method will fail due to a NULL value even i think this should not be true: Provisionsabrechnung.UnitTests.Provisionsrechner.Test_Aggregate_Sum_with_Null: System.InvalidOperationException : Das Objekt mit Nullwert muss einen Wert haben. public void Test_Aggregate_Sum_with_Null() { ProvisionsabrechnungDataContext dataContext = new ProvisionsabrechnungDataContext(); Objektverkauf o1 = new Objektverk...

Outlook 2003
I used to be able to do this in Outlook 2000. The string will be in either the subject or text body...or both. For example: looking for an email message about a specific invoice number 12345. Now, it does not find the number even when I know it's there....and can find it by hunting on my own. Thanks for any help you can offer~ "Hello_It''''s_Me-CA" <HelloItsMeCA@discussions.microsoft.com> wrote in message news:B942C394-5363-49BE-AFB3-04E34C1CE4BF@microsoft.com... >I used to be able to do this in Outlook 2000. The string will be in either > t...

mscvr71.dll help
How do I make my VS.net 2003 software not depend on msvcr71.dll? Thanks. Use static linking. I don't know where to set the option in VS7, but it used to be under Code Generation where you selected the desired C runtime library to use. In VS6 we had a choice between a dynamically-linked runtime and a statically-linked runtime. I've not had to make this choice under VS7 so I'm not sure where, in their overly-clever and completely gratuitous reimplementation of the user interface, this has been hidden. joe On Sat, 21 Feb 2004 22:24:56 GMT, anonymous@coolgroups.com wrote: &g...

Help with ShowFilter Macro
I'm trying to use this ShowFilter UDF written by Tom Ogilvy (see bottom of post). It says to use... =showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32) ....in a cell to show the criteria for Column B. For one thing, I don't understand the cells B2 and B3 business. What is supposed to be in those cells? I would like this function to appear in the cell directly above or below the Autofilter menu cell. How do I change the function if the Autofilter criteria menu is in, cell A3, for example, and I want the criteria (this function) to appear just above, in cell A2? After trying all so...

Excel 'hangs' when deleting a row
when i delete a row in excel 2000, everything locks up! and when lookup at Task Manager/Processes, it's MEM USAGE goes up to 22K o more! i tried doing it in another file but i experienced no error. the dat is in LAN. all data are filtered when the deletion began. however i ca delete files before. please help, anyone.... -- Message posted from http://www.ExcelForum.com The best thing is to free up memory (assuming your file is large). Clos all applications you do not require at the time and any workbooks no in use. Dunca -- Message posted from http://www.ExcelForum.com ...

Summary of Difference between dates in years, months, days
I need to calculate the difference between 2 dates and then total them. Here's what I have so far: From To Length of Service 01/09/2003 31/01/2010 6y 4m 30d 01/06/2000 30/11/2002 2y 5m 29d Total of Service: ?????????? I've used the following formula to calculate the total days worked: =DATEDIF(A4,B4,"Y")&"y "&DATEDIF(A4,B4,"ym")&"m "&DATEDIF(A4,B4,"md&...

Help with automating file name
I have the following code that exports the below query to excell. I would like the files name to include the month and date. How would I format this? DoCmd.OutputTo acOutputQuery, "qryShopOrderSqFtShippedSummaryExport",_ acFormatXLS, "W:\Cokato\Production\ProdRoomRpt.xls" -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200708/1 Hi, Matt. > I would > like the files name to include the month and date. Uh, . . . the date _always_ includes the month, unless you're r...

Please help with last formula for order form.
I am able to accomplish this with 1 column by the formulas below. Cell H160 is the subtotal: =IF(SUM(H72:H111)>0,SUM(H72:H111),"") Cell H166 the total: =IF(SUM(H160)>0,SUM((H160*H163)+H160),"") Cell H163 is for Tax. I am almost finished creating an order form. I would like to get the SUM of 3 different columns that are separated. I am not able auto fill strait down the column, because the information is separated in groups with titles, and the cells are not identically sized. I tried varations of this formula: =IF(SUM(H72:H111)+(116:131)+(135:154)>0,SUM ((H72:H...

Combine 2 rows if name is same in Column B & C on both
Combine 2 rows if name is same in Column B & C on both 2 spreadsheets - Sheet 1 is bigger with extra names in column B & C Lastname Firstname Both - Column B & C Lastname Firstname - both sheets Sheet 1 has data in Col. D & E Sheet 2 has data in Col. F & G Sheet 1 has extra names not in Sheet 2 If Sheet 1 B&C = Sheet 2 B&C , then add F&G columns from sheet 2 , behind D& E columns on sheet 1 , for the match of names in Column B & C Thanks kerns.walter@epa.gov On Nov 13, 12:50 pm, wk <kerns.wal...@epa.gov> wrote: > Combine 2 rows if name...

Macros #35
Hi I run a daily download from another system, via a .txt file into Excel. Each day the WorkSheet has a different name ie A3_12_11_04 then tommorow it will be A3_13_11_04 etc to represent the date it was downloaded. I then have to create a macro and pull off some of of the data on a daily basis...this is where my problem arrisses. How can my Macro recognise the different Worksheet name on a daily basis? My 2 thoughts would be to get data from another open worksheet or be able to put a promt in my Maco to search the name of the Worksheet. Confused...I am!! Any Help would be welcome. ...

SmartList Restrictions help
I built a SmartList that is based on the Year-to-Date Transaction Open file, and has the Account Master linked to it. I want to restrict it to accounts that begin with 36, 38, or 2504. I tried adding a restriction that says "Account Number:Account_Segment_Pool1 begins with 36 OR 38 OR 2504", but I got no results when I did it that way. I also set up 3 separate restrictions, but that didn't work either. Is this possible? I can't find much information about how to write restrictions in SL Builder. ...

Conditional formatting help #4
My problem is that, that i want to ignore blank i mean i had set a conditional formatting say A B C D 24.9 25.9 25 25.8 22.6 23.4 22.5 23.3 If value in ColA is less than value in ColC, cell A1 is shaded blue OR if value in ColB is greater than value in ColD, cell B1 shaded blue. I have done above formatting but my problem is that if i dont enter anything in colC then also colA is shaded in blue similarly if i dont enter any value in colD then also col B is shaded.I mean i want to ignore the blank.I need , if col C is blank then the Col A must be normal .& if col D is blank & i ent...

help plz
my account has been inactive how to i reacctivate it? What account and what does this have to do with Outlook? "heather" <tracyliepke@yahoo.com> wrote in message news:066001c36c53$bb68d180$a501280a@phx.gbl... > my account has been inactive how to i reacctivate it? ...

Help with queries
Hi Guys, This is the first time after school that I am trying to use ms access at work and i need ur help in creating a query. Any help will be highly appreciated!! Here is what I need... I have relatively small ms acces database with about 1000 I have 3 colums date ipaddress sitename 12/09 34.3.3.3 A 12/09 34.3.3.3 A 12/09 34.3.3.3 A 12/09 33.4.4.4 B 12/09 33.4.4.55 C What i need is if an ipadress is recorded more t...

macro
hi, i have various worksheets and within that worksheet there are cells having #DIV/0!. I want this to be replace by zero. i know there is a formula which will give out zero but if someone can write a macro would good. Thanks Sub ErrorTrapAdd() Dim mystr As String Dim cel As Range For Each cel In Selection If cel.HasFormula = True Then If Not cel.Formula Like "=IF(ISERROR*" Then mystr = Right(cel.Formula, Len(cel.Formula) - 1) cel.Value = "=IF(ISERROR(" & mystr & "),0," & mystr & ")" ...

How to sort with merged rows
This is my delema. I have data that I need to input. At the same time I would like to have a blank area below each contact so that I can add notes. The first problem is that when I try to sort, excel keeps saying "this operation requires the merged cells to be identically sized" Even if I do get by that problem, how can I keep the notes and the contact/data info above it together when I use a sort. Chris wrote: > This is my delema. I have data that I need to input. At the same time I > would like to have a blank area below each contact so that I can add notes. >...

Need help to choose loyalty program integrated with website
Hello. My name is Alex and I am working for franchise company using RMS system. We are looking for loyalty program integrated with web store. We have 12 franchisee stores using RMS and they are all conneted to our HQ system in main office. We want customers to earn point for each sales and redeem their points only at our website. (not on off-line store) Can anybody recommend best solution for our plan? Thank you. ...

Merging of rows.
I have a excel sheet in which there is data in only one column. The data is spread in 2 or 3 rows and after that there is blank row. The data spread in these row is related to one row. I want to bring this spread data in one row. Blank row can be as it is between ro useful data rows. Maybe a macro would do it: Option Explicit Sub testme() Dim CurWks As Worksheet Dim NewWks As Worksheet Dim DestCell As Range Dim BigArea As Range Dim SmallArea As Range Set CurWks = Worksheets("Sheet1") Set NewWks = Worksheets.Add Set DestCell ...

highlighting rows in excel
I've got the following type of data: id name address postcode 1 davie london lon34 1 davo glasgow ga23 3 tester manchester ma45 3 tas sfdfs sdfs 4 adssf 4asfd sf 4 dfsdf sdfsdf sdfh44 How can I highlight the rows with 1, 1, 3, 3, and 4,4 with alternating background colours? for example the first match would be blue(1,1) the second match would be yellow(2,2) the third match would switch back to blue (3...

compare two columns with different ranges in two worksheets
I need to compare two columns of data in two different worksheets and display a third one. Here it is an example: -(worksheet1!A1:A10), (worksheet1!B1:B10) and (whorksheet2!C1:C25) -this is my query, if C5 is already in (A1:A10) I want to display B5 in worksheet2!D5 I think it is tricky because you need to identity which row in the A1:A10 is equal to C5 to display B5 and the range are different. you could save my day chris90 In worksheet2!D1: =if(isna(vlookup(C1, worksheet1!$A$1:$B$10, 2, 0)), "", vlookup(C1, worksheet1!$A$1:$B$10, 2, 0)) HTH Kostis Vezerides brilliant, ma...