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?
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.
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
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).
Feeling like a fly on the inside of a closed window (Thunk!)
If I post a lot, it is because I am bored at work! ;D Or stuck...
* Anything I post can be only my opinion. Advice etc is up to you to persue...
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.
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"
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
If you find any of my posts of good help, please rate it
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
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
If you find any of my posts of good help, please rate it
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??
Reply With Quote
Originally Posted by facial^
