Excel query

  fermerboy 01:22 06 Jan 2004

Hi all
I have a cashbook with many columns I want to total every month after that months entries are done. I want to use a macro to do this with a button so its simple for my old folks to use. I can get all the macro bit to work except the sum. At the minute I use autosum and then drag the formula along the line I want the totals on. Autosum always totals from the last autosum line(previous months total)no bother, but when I record it on to a macro it always sums the same number of lines, so it won't work if I have more entries the following month. What formula should I use for the sum to make it always add from the last sum??? Any help appreaciated. I am not so hot on excel techie stuff. It will be evening tomorrow before I can reply. Thanks

  xania 09:08 06 Jan 2004

I have never used macros but have worked with a spreadsheet in the way you mention. What I do is set up the total rows and columns each with a simple total, which does expand as you add new linesd/columns, and, of course, a cross-cast oin the bottom right-hand corner. There is an important poiint to bear in mind, however, and that is that, if you add a cloumn in between the last and the total (ditto for lines) it will not expand, so you need to leave a blank column/line to be sure.

Others may have a macro solution that works better, but, for me simplest is best.

  Big Elf 10:36 06 Jan 2004

What about putting the AutoSum button on the icon bar and then get them to double click on that.

  fermerboy 22:55 06 Jan 2004

Thanks for those ideas, I'd like to get a macro to do it though, I format the row to bold and insert a line as well to make the total easier to read. I don't like the technology getting the better of me either. What books are the best ones to teach yourself excel?

  VoG II 23:02 06 Jan 2004

In an Excel macro, to find the last row containing data the code is

LastRow = Cells(Cells.Rows.Count, iCol).End(xlUp).Row

Where iCol is a number representing the column (1=A, 2=B, etc.). Does this help?

  fermerboy 00:02 07 Jan 2004

Thanks VoG
I think that is a bit over my head really, I think I need to try and school up on some of this stuff before I get into code and things. I kind of understand what your line means but its a bit complicated for me at this stage just now.
I can build a pc from scratch, format hard drives, do dual boot installs, sort out no end of internet/modem stuff and all kinds of stuff in Access but I haven't a bloody clue with excel!!!!!
Thanks for your input, I note that you are the master in this stuff from past threads!!

Please find below the code required to do as you ask based on the following assumptions:

1. The first column will always contain an entry like a date entry

2. That there is a clear row between the Totals for the last month and the start of the next month - although this could be got round by looking for the word Totals instead of the blank line

Copy all between the == lines and paste into a module in your workbook

Sub SumIt()

Dim Lastrow As Integer, PreviousRow As Integer

Dim LastColumn As Integer, iCol As Integer

Dim MyRange As Range

Lastrow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

PreviousRow = Worksheets("Sheet1").Cells(Lastrow, 1).End(xlUp).Row

LastColumn = Worksheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column

Range(Cells(Lastrow, 1), Cells(Lastrow, LastColumn)).Select

With Selection.Borders(xlEdgeBottom)

.LineStyle = xlContinuous

.Weight = xlMedium

End With

Rows(Lastrow + 1).Font.Bold = True

Cells(Lastrow + 1, 1) = "Totals"

For iCol = 2 To LastColumn

Set MyRange = Range(Cells(PreviousRow, iCol), Cells(Lastrow, iCol))

Cells(Lastrow + 1, iCol) = Application.WorksheetFunction.Sum(MyRange)

Next iCol

Cells(Lastrow + 3, 1).Select

End Sub


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

There’s more to Finnish artist Tove Jansson than creating the Moomins

iPhone X news: Release date, price, new features & specs

Comment regarder la Coupe du monde de rugby à XIII en direct ?