Excel Help

I'm tring to auto sum a column that will change in lenght from sheet to sheet. I'm writing a macro to do this for people that really don't understand how to calculate metrics. Any idea's on how to make this work?

ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(3, -2).Select
ActiveCell.FormulaR1C1 = "Total Points"
ActiveCell.Offset(1, 0).FormulaR1C1 = "Total Missed"
ActiveCell.Offset(2, 0).FormulaR1C1 = "Subtotal"
ActiveCell.Offset(3, 0).FormulaR1C1 = "Points Taken"
ActiveCell.Offset(4, 0).FormulaR1C1 = "Delta"
ActiveCell.Offset(-2, 0).Select
ActiveCell.FormulaR1C1 = "=SUM"
ActiveCell.Offset(0, 1) = "=SUM" <-Column "K"
ActiveCell.Offset(0, 1) = "=SUM" <-Column "L"
ActiveCell.Offset(0, 1) = "=SUM" <-Column "M"

Remember that the K column will not always be the same number, so how do I select "K1" and then the last value of K column? :ehh:

You will get better exposure in our Office Development forum :)

I'm a noob at these forums! Can I move it or do you have too?

Already done. ;) :)

Can you just use the sum of the entire column?

VB Code:

ActiveCell.Offset(0, 1) = "=SUM(K:K)"

No, but it put a funny blue dot in the cell that I have never seen before. It's wanting a cirular reference!

No, but it put a funny blue dot in the cell that I have never seen before. It's wanting a cirular reference! I was thinking something like =SUM(K2:Kcolumn)...but that didnt work...

Well if your active cell is in column K then its a circular reference and an error. You shouldnt be setting a formula to add itself up. It should be in another cell/column.

If you know roughly how many rows you are likely to have at the max, set the sum function to the row above the title row, then set the arear to the start of data to the average plus 2000 rows. Depending on how much data you get, it should work fine.

Alternative is to get the last row (assuming all blank below and that the data is continous above):

Code:

---

?activesheet.cells(65530,11).End(xlup).Row

---

Put that in the immediates window (I usually set the activesheet to a worksheets object).

ActiveCell.Offset(1, 0).FormulaR1C1 = "Total Missed"
ActiveCell.Offset(2, 0).FormulaR1C1 = "Subtotal"
ActiveCell.Offset(3, 0).FormulaR1C1 = "Points Taken"
ActiveCell.Offset(4, 0).FormulaR1C1 = "Delta"
ActiveCell.Offset(-2, 0).Select
ActiveCell.Activate
ActiveCell.Offset(0, 1) = "=SUM(ColumnL)"
ActiveCell.Offset(0, 2) = "=SUM(columnM)"

Looking at this code, the first line in red I am slecting a cell. What I want to do is auto sum that row and then move over to the right one cell and autosum that row and then move over again and auto sum that row. The thing I don't understand is how to get the row above the selected cell to autosum with code.

Hi, I think I am trying to do the same thing. I am writing a macro to run on a spreadsheet that will always have a different number of rows of data. What I am trying to do is to be able to put a "total" cell at the bottom of the last column.

Currently I have:

Selection.End(xlToRight).Select
Selection.End(xlDown).Activate
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
ActiveCell.FormulaR1C1 = "=SUM(??)

What I want is code to replicate the shortcut "ALT + =" which automatically sums from whichever cell you are in up to the top of that block of numbers.

I have found the following code on another forum which seems to have worked but it is to sum rows and not sum columns. I've never really been taught vb, so I can't quite follow each step and so I don't know how to alter it.

r = ActiveCell.CurrentRegion.Rows.Count
Set SumRow = ActiveCell.CurrentRegion.Offset(r, 0).Resize(1)
SumRow.FormulaR1C1 = "=SUM(R[" & -r & "]C:R[-1]C)"
SumRow.Offset(0, -1).Resize(1, 1).Value = "Totals"

Quote:

---

Originally Posted by facial^

ActiveCell.Offset(1, 0).FormulaR1C1 = "Total Missed"
ActiveCell.Offset(2, 0).FormulaR1C1 = "Subtotal"
ActiveCell.Offset(3, 0).FormulaR1C1 = "Points Taken"
ActiveCell.Offset(4, 0).FormulaR1C1 = "Delta"
ActiveCell.Offset(-2, 0).Select
ActiveCell.Activate
ActiveCell.Offset(0, 1) = "=SUM(ColumnL)"
ActiveCell.Offset(0, 2) = "=SUM(columnM)"

Looking at this code, the first line in red I am slecting a cell. What I want to do is auto sum that row and then move over to the right one cell and autosum that row and then move over again and auto sum that row. The thing I don't understand is how to get the row above the selected cell to autosum with code.

---

Hey facial,
Could you post a small workbook example (in .zip format) for us to take a better look at? There's plenty of ways to do what you are doing but I don't understand the logic behind this.

Thanks
Joseph

Here's the file!

facial

Here's an example book that I hope will give you a good start to what you are trying to do. Just so you know, I'm sure the criteria I used for "Total Points" and "Total Missed" aren't what you want. But this is just an example of what you can do.

Here's the code:

VB Code:

Option Explicit
 
Sub AutoSumSheet()
Dim ColHeaders() As Range
Dim cell As Variant
Dim i As Long
 
ReDim ColHeaders(0)
 
For Each cell In Range("A1", Cells(1, Columns.Count).End(xlToLeft))
    If cell = "TotalScheduledItems" Or cell = "NotSampled" Then
        ReDim Preserve ColHeaders(UBound(ColHeaders) + i)
        Set ColHeaders(i) = cell
        i = i + 1
    End If
Next cell
 
Set cell = Cells.SpecialCells(xlCellTypeLastCell)
cell.Offset(3, -2) = "Total Points"
cell.Offset(4, -2) = "Total Missed"
 
For i = LBound(ColHeaders) To UBound(ColHeaders)
    If ColHeaders(i) = "TotalScheduledItems" Then
        cell.Offset(3, -1).Formula = "=Sum(" & Cells(ColHeaders(i).Row + 1, ColHeaders(i).Column).AddressLocal(0, 0) & _
            ":" & Range(ColHeaders(i).Address).End(xlDown).AddressLocal(0, 0) & ")"
    ElseIf ColHeaders(i) = "NotSampled" Then
        cell.Offset(4, -1).Formula = "=Sum(" & Cells(ColHeaders(i).Row + 1, ColHeaders(i).Column).AddressLocal(0, 0) & _
            ":" & Range(ColHeaders(i).Address).End(xlDown).AddressLocal(0, 0) & ")"
    End If
Next
End Sub

Notice in the sample workbook I made the last column have a different amount of rows in it and the formulas are set up to only sum how many rows are being used in that particular column.

HTH :thumb:

Thanks, I think that gives me something to work with. I'm still learning VB now and I hope I can sharpin my skills as time goes on.

Another thing that I've been working on is adding a drop down box for an application that runs on pocket pc. For instance, I install the program on the handheld pc, then add the drop down box thats blank! When users enter data in the drop down box, somewhere on the pc it stores that information so that when the same user wants to enter the same infromation ;) its in the drop down box and they dont have to retype the same thing. This could be used for user names when logging on the applicatioin. If for any reason the handheld pc had to be hard reset (clearing everything) then the users would have to start all over again from scratch with the drop down box. Any Ideas?? :ehh: