PDA

View Full Version : VBA to automate a process



jo15765
05-10-2012, 06:55 PM
I have an excel spreadsheet that pulls in an access query and it set's up some a small table in excel that looks like this:
Richard 14 =C13/C17
Joseph 8 =C14/C17
Mark 10 =C15/C17
Elijah 2 =C16/C17
--The above rows are the access query
--The below row is input manually
Total Sales =Sum(C13:C16) =SUM(D13:D16)

What I am wanting to do is remove the total sales row, and have VBA calculate that for me. So my question is, how can I have VBA delete the "Total Sales" Row and add it via VBA to add "Total Sales" in B17 the Sum of the above rows in C17 (of course that could change depending on the query), and the 2nd sum in cell D17 (of course this is a percentage idk if that matters for VBA code) and this could also change depending on the query output.

Is this possible to do?

EDIT ----
I uploaded a sample workbook to help better illustrate what I am wanting to accomplish, it is an .xlsx format since that is what I use at home, but I need the code to work on a .xls (excel 2000) because that is what I will be running the code on. Sorry to be difficult!

Bob Phillips
05-11-2012, 01:01 AM
Dim startCell As Range
Dim endCell As Range
Dim rng As Range

With ActiveSheet

Set startCell = .Range("B1")
Set endCell = startCell.End(xlDown)
Set rng = .Range(startCell, endCell.Offset(-1, 0))
endCell.Formula = "=SUM(" & rng.Address(False, False) & ")"
rng.Offset(0, 1).Resize(rng.Rows.Count).Formula = "=B1/B$" & endCell.Row
endCell.Offset(0, 1).Formula = "=SUM(" & rng.Offset(0, 1).Address(False, False) & ")"
End With

jo15765
05-11-2012, 07:14 AM
The above code is using the last row as the total row...what I am wanting to do is to create the last row with Total Sales then the formula then the other formula based off the data in the above cells.

I am wanting to have an approach in place for when the query overwrites my total row, be able to have a macro automatically run, that re-creates that row instead of manual intervention having to do it.

Bob Phillips
05-11-2012, 07:58 AM
Yes it did overwrite the total row, but that aside it did exactly what you describe. You can't put a total row below the existing total row that then sums up that (previous) total row.

jo15765
05-11-2012, 11:18 AM
I just re-ran it and for some reason it's not adding the total row. I did have to modify the code slightly to accommodate for a different row (I love how things have been the same for 2 years, and then they want it changed :wot) so maybe I actually muffed up the code you had working. This is what I am working with:


Sub Test()
Dim startCell As Range
Dim endCell As Range
Dim rng As Range

With ActiveSheet
Set startCell = .Range("C73")
Set endCell = startCell.End(xlDown)
Set rng = .Range(startCell, endCell.Offset(-1, 0))
endCell.Formula = "=SUM(" & rng.Address(False, False) & ")"
rng.Offset(0, 1).Resize(rng.Rows.Count).Formula = "=C73/C$" & endCell.Row
endCell.Offset(0, 1).Formula = "=SUM(" & rng.Offset(0, 1).Address(False, False) & ")"
End With
End Sub

jo15765
05-16-2012, 07:42 PM
Yes it did overwrite the total row, but that aside it did exactly what you describe. You can't put a total row below the existing total row that then sums up that (previous) total row.

You are correct, it does do exactly as I requested. Apologies for not seeing it sooner (it took me a week!). The problem I was having is that what I said, and what I wanted were two different things, so let me try to clarify just a little....

I figured it out....the data is creating a "Total Row" and summing as requested. Let me clarify a little bit as to what I am needing. Instead of the code over writing the last row with data, I want it to find the last row with data and go down to the next row and create the total and sums there. Is that difficult to modify?

Bob Phillips
05-17-2012, 12:32 AM
Dim startCell As Range
Dim endCell As Range
Dim rng As Range

With ActiveSheet

Set startCell = .Range("B2")
Set endCell = startCell.End(xlDown)
Set rng = .Range(startCell, endCell.Offset(-1, 0))
endCell.Offset(1, 0).Formula = "=SUM(" & rng.Address(False, False) & ")"
rng.Offset(0, 1).Resize(rng.Rows.Count).Formula = "=B1/B$" & endCell.Row
endCell.Offset(1, 1).Formula = "=SUM(" & rng.Offset(0, 1).Address(False, False) & ")"
End With

jo15765
06-26-2012, 08:06 PM
Perfectly! Sorry for my misunderstanding the original code. I have encountered a different set-up now (the boss changed some of the workbooks). Now the "startRange" that is being used in the code could be in B2, B3 or B4!!! There may not be, but is there a way to accomodate the start cell being in one of those 3???? One thing that may play into my favor is the fact that the cells above the start cell will be blank. For example, let's say the start cell is B4...then B2 and B3 would be blank. Or if the start cell was B3, then B2 would be blank.

Thanks again folks :)

Bob Phillips
06-27-2012, 01:02 AM
Dim startCell As Range
Dim endCell As Range
Dim rng As Range

With ActiveSheet

If Range("B2").Value = "" Then

If Range("B3").Value = "" Then

Set startCell = Range("B4")
Else

Set startCell = Range("B3")
End If
Else

Set startCell = .Range("B2")
End If

Set endCell = startCell.End(xlDown)
Set rng = .Range(startCell, endCell.Offset(-1, 0))
endCell.Offset(1, 0).Formula = "=SUM(" & rng.Address(False, False) & ")"
rng.Offset(0, 1).Resize(rng.Rows.Count).Formula = "=B1/B$" & endCell.Row
endCell.Offset(1, 1).Formula = "=SUM(" & rng.Offset(0, 1).Address(False, False) & ")"
End With

jo15765
07-03-2012, 07:57 PM
That's awesome!! :) :) :)

I recorded a macro to do this, but it doesn't exactly hit the nail on the head...how could I then use the above code as my "source data" and create a pie chart in VBA?

Bob Phillips
07-04-2012, 12:47 AM
Just do it with the macro recorder on, it should get you started.