Macro to sum costs in a worksheet (1 Viewer)

Groundrush · Jan 9, 2008

I have imported data from Access into an Excel worksheet, created a basic macro to perform some recurring tasks.
The problem I have is that I still need to manually manipulate a lot of the data myself & it's still extremely time consuming

Is there a way to sum the total of a field using a macro?
I found that you can't select the cell to use for the total because the record count varies each time the data is exported

Any Ideas?

Thanks

Brianwarnock · Jan 9, 2008

This could depend on what the worksheet looks like.
You can find the last row/column in a worksheet and perhaps you could also identify other columns in some way by specific content .

Brian

Groundrush · Jan 10, 2008

Here is an example of a cut down version of what my db query & Excel spreadsheet looks like

I use the query called "qryStatementForecast" & import the results into excel then run the macro
(code attached in notepad)

I then manually manipulate the data into the tabs they belong to
(See attached spreadsheet called "Statement example"

The excel spreadsheet is a basic copy of the end result of what I'm trying to achieve

Any advice to how I can improve on this will be much appreciated.

thanks

scott-atkinson · Jan 10, 2008

Groundrush said:
Is there a way to sum the total of a field using a macro?
I found that you can't select the cell to use for the total because the record count varies each time the data is exported

Any Ideas?

Thanks

Groundrush.

Because the reord count varies, you are in deed correct that you cannot designate a cell to use as the summing cell.

What you can do, is select in your macro a cell where you know you will have some of the data that you want to sum, perform an .xldown function this will take you to the end of the selection, as if you are doing a ctrl shift down keystroke, then use the Offset function, this will move you to a defined number of cells either to the left or down of the last cell, here you can then define as the active cell and put in your sum.

Groundrush · Jan 10, 2008

scott-atkinson said:
Groundrush.

Because the reord count varies, you are in deed correct that you cannot designate a cell to use as the summing cell.

What you can do, is select in your macro a cell where you know you will have some of the data that you want to sum, perform an .xldown function this will take you to the end of the selection, as if you are doing a ctrl shift down keystroke, then use the Offset function, this will move you to a defined number of cells either to the left or down of the last cell, here you can then define as the active cell and put in your sum.

I was able to use the xldown in the macro but it ends on the last record instead of the next row below where the totals will go, I take it that where the offset function comes in.

but I don't now how to code that in the macro

.

Brianwarnock · Jan 10, 2008

Other than deducing that you must be working in/for Liverpool I didn't get very far. Can I assume that EFORMREF would enable you to put rows from all on to the correct sheet? But how is the breakdown for the summary sheet arrived at?
As to using Offset then the code below would select the lastcell in the used range of sheet1

Worksheets("Sheet1").Activate
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate

Selection.Offset(1, 0).Select
would move the selection down 1 row

Selection.Offset(0, 1).Select
would move the selection 1 col to the right

negative numbers move you in the opposite direction

But the big question is why are you doing what appears to be a report in EXCEL , wouldn't it be easier to stay with ACCESS?

Brian

Groundrush · Jan 11, 2008

Brianwarnock said:
But the big question is why are you doing what appears to be a report in EXCEL , wouldn't it be easier to stay with ACCESS?

Brian

I would love to do it in Access but sadly as always people don't use the database as designed & alot of records need to be altered manually to suit the requirements, you know the saying "You can only get out what you put in"

I do work in Liverpool, perhaps next door to you, maybe not because if that was the case my database would probably work....lol

Brianwarnock · Jan 14, 2008

I'm not sure where we are on this , are you now able to do your totalling?

Brian

Groundrush · May 2, 2008

Brianwarnock said:
I'm not sure where we are on this , are you now able to do your totalling?

Brian

Hi Brian,

Sorry for late reply, I've just been browsing through all my old posts & found this one.

I was not able to find a solution & have got used to having to manually sum up the fields when I run the report...lol

The main problem was that the record would differ each time I run the report & I could not find a way to catch the last line & total all the costs.

Brianwarnock · May 3, 2008

I am attaching a sample that includes a macro to insert the Total row in the Sheet ALL, this can be used to put a total row on sheets with a single row. I think you have one sheet with 2 total rows, that will require more work to automate as identifying the last row of data will be more difficult.

Brian

Groundrush · May 4, 2008

Thanks Brian

I'll let you know how I get on

Groundrush · Sep 7, 2008

Brianwarnock said:
identifying the last row of data will be more difficult.

Brian

Anyone know how to get to the last row?

thanks

Brianwarnock · Sep 7, 2008

This is a blast from the past.

If I remember it is the summary sheet that is the problem with 2 banks of data. The other sheets are covered in my previous replies I think?

Below is code I worked on today, it refers to sheet1 because that was the sheet my testing was done on.

It assumes that there are just the 2 banks of data with blank lines between.

Brian

Code:

Sub totalssheet1()

' This is to calculate totals on sheet1 where there are 2 groups of rows of data in cols g to k
' both groups cab vary in the number of rows
' the code assumes the first group starts in row 3
'Brian Warnock September 2008

Dim tcell As Range      ' totals for first group
Dim tcell2 As Range     ' totals for second
Dim scell As Range      ' start of second

Sheets("sheet1").Select
Set tcell = Range("g3").End(xlDown).Offset(1, 0) 'Totals cells

tcell.Value = Application.WorksheetFunction.Sum(Range("g3", Range("g3").End(xlDown)))
tcell.Offset(0, 1).Value = Application.WorksheetFunction.Sum(Range("h3", Range("h3").End(xlDown)))
'tcell(offset(0,2).Value = Application.WorksheetFunction.Sum(Range("i3", Range("i3").End(xlDown)))
'etc
'format
Range(tcell, tcell.Offset(0, 4)).Select
With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .Weight = xlThick
    End With
        With Selection
        .NumberFormat = "$#,##0.00"
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        End With

'then get next section
Set scell = Cells(tcell.Row, tcell.Column).End(xlDown)

Set tcell2 = Cells(scell.Row + 1, scell.Column).End(xlDown).Offset(1, 0)
tcell2.Value = Application.WorksheetFunction.Sum(Range(scell, tcell2.Offset(-1, 0)))
tcell2.Offset(0, 1).Value = Application.WorksheetFunction.Sum(Range(scell.Offset(0, 1), tcell2.Offset(-1, 1)))
'etc

Range(tcell2, tcell2.Offset(0, 4)).Select
With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .Weight = xlThick
    End With
        With Selection
        .NumberFormat = "$#,##0.00"
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        End With

End Sub

Brianwarnock · Sep 8, 2008

I've edited the code to remove 2 lines left in from testing, sorry about that.
If I get a chance today going to write code to put the formulae in the cells rather than the calculation as that will be more flexible.

Brian

Groundrush · Sep 8, 2008

Thanks Brian,

I tried myself & altered your code to this, I'm testing it now with different size cell counts.

Code:

Sub totalsAll()


Dim tcell As Range

Sheets("All").Select
Set tcell = Range("I2").End(xlDown).Offset(1, 0) 'Totals cells

tcell.Value = Application.WorksheetFunction.Sum(Range("I2", Range("I2").End(xlDown)))
tcell.Offset(0, 1).Value = Application.WorksheetFunction.Sum(Range("J2", Range("J2").End(xlDown)))
tcell.Offset(0, 2).Value = Application.WorksheetFunction.Sum(Range("K2", Range("K2").End(xlDown)))
tcell.Offset(0, 3).Value = Application.WorksheetFunction.Sum(Range("L2", Range("L2").End(xlDown)))
tcell.Offset(0, 4).Value = Application.WorksheetFunction.Sum(Range("M2", Range("M2").End(xlDown)))


Range(tcell, tcell.Offset(0, 4)).Select
With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .Weight = xlThick
    End With
        With Selection
        .NumberFormat = "$#,##0.00"
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        End With



End Sub

Groundrush · Sep 9, 2008

Hi Brian,

That code works pretty well, I don't have the record count problem like before

Thanks

Brianwarnock · Sep 9, 2008

Glad I got something right , its years since I did any of this.

Brian

Macro to sum costs in a worksheet (1 Viewer)

Groundrush

Registered User.

Brianwarnock

Retired

Groundrush

Registered User.

Attachments

scott-atkinson

I'm with the Witch.......

Groundrush

Registered User.

Brianwarnock

Retired

Groundrush

Registered User.

Brianwarnock

Retired

Groundrush

Registered User.

Brianwarnock

Retired

Attachments

Groundrush

Registered User.

Groundrush

Registered User.

Brianwarnock

Retired

Brianwarnock

Retired

Groundrush

Registered User.

Groundrush

Registered User.

Brianwarnock

Retired

Similar threads

Users who are viewing this thread