VBA - Autosum code needed! Please someone help!!!!

[sgn75]

I need visual basic code help!!!

Situation:

I have to run a report everyday where the numbers in column "G" need to be totalled. I want to write a macro to do this. The problem is that the number of rows in column "G" are different everyday.

Goal:

I would like to have the macro act like the Autosum button in excel, totalling the numbers, in however many rows are in column "G", and displaying the answer in the cell beneath the last row in column "G".

Code I tried:

This code got me the answer to display in a Message Box, but I need it to appear on the spreadsheet, in the cell beneath the last row in column "G".

answer = Application.WorksheetFunction.Sum(Range("G1", Range("G1").End(xlDown)))
MsgBox answer

Anyone please help me!!!!
sgn75

 

[Andrew Poulsom]

Try this:

Sub Test()
    Dim Rng As Range
    Dim c As Range
    Set Rng = Range("G1:G" & Range("G1").End(xlDown).Row)
    Set c = Range("G1").End(xlDown).Offset(1, 0)
    c.Formula = "=SUM(" & Rng.Address(False, False) & ")"
End Sub

 

[Al Chara]

I need visual basic code help!!!

Situation:

I have to run a report everyday where the numbers in column "G" need to be totalled. I want to write a macro to do this. The problem is that the number of rows in column "G" are different everyday.

Goal:

I would like to have the macro act like the Autosum button in excel, totalling the numbers, in however many rows are in column "G", and displaying the answer in the cell beneath the last row in column "G".

Code I tried:

This code got me the answer to display in a Message Box, but I need it to appear on the spreadsheet, in the cell beneath the last row in column "G".

answer = Application.WorksheetFunction.Sum(Range("G1", Range("G1").End(xlDown)))
MsgBox answer

Anyone please help me!!!!
sgn75

sgn75,

That was a really nice post. Very clear and detailed. It would have been impossible not to get a good reply.

 

[sgn75]

Thank you!!!

Thank you very much for the code! I had been racking my brains for a while trying to figure it out. This is a great message board, I will participate more often!

 

[GarryF]

Thanks for the code, works a treat. I have a spreadsheet where I need to sum 3 columns, P, Q and R and although I could get P and R to work, trying to auto sum Q gave a Run time error - 1004 Application defined or Object defined error. The code I ws using was Range("P" & CStr(LRow)).Select
Selection.Formula = "=SUM(P2:" & Format(ActiveCell.Row - 1, "P#") & ")"
and simply entering the same code but changing the "P" value to "Q" or "R" - works fine for P and R but always fails for Q - spooky. Anyway your code works spot on so thanks

 

[3Point3Ghz]

This code works great for what I'm doing also. Although I am running into one issue. I use the same macro for several sheets of inventory data, the only difference is the cell contents. Formatting, headings, everything else is the same. For some reason my code runs perfectly on the first sheet, and on the second it skips over placing the formula in cell G1. I've stopped the code immediately after that line and the cells I need to create the formula are selected, it's just not creating the formula for some reason. Any thoughts?


' Add formula for GL total cost at top of page
Dim Rng As Range
Set Rng = Range("G3:G" & Range("G3").End(xlDown))
Range("G1").Formula = "=SUM(" & Rng.Address(False, False) & ")"

 

[GarryF]

Hi 3Point3Ghz, I'm no expert, but I suspect you need to include the sheet name in your formula, so that it is specific to the sheet you are referencing. Something along the lines of
Set Rng = Range("Sheet1!G3:G" & Range("Sheet1!G3").End(xlDown))
Range("Sheet1!G1").Formula = "=SUM(" & Rng.Address(False, False) & ")"

where Sheet1 is the specific name of the sheet you are accessing - Note you could pass the "Sheetname" from an array if you have several sheets to work with. I'm sure there are better ways, but it may help in the meantime.

 

[3Point3Ghz]

Hi 3Point3Ghz, I'm no expert, but I suspect you need to include the sheet name in your formula, so that it is specific to the sheet you are referencing. Something along the lines of
Set Rng = Range("Sheet1!G3:G" & Range("Sheet1!G3").End(xlDown))
Range("Sheet1!G1").Formula = "=SUM(" & Rng.Address(False, False) & ")"

where Sheet1 is the specific name of the sheet you are accessing - Note you could pass the "Sheetname" from an array if you have several sheets to work with. I'm sure there are better ways, but it may help in the meantime.

Thanks for the thought. I haven't attempted that fix as of yet, I'm trying to keep this code universal if I can. It's being used on our monthly inventory sheets that are pulled out of the database. As of now there are just two different sheets that I'm running this code on, but that could change at any time and I'm trying to avoid making it more complicated than it has to be. Right now I can merge the sheets together into one workbook and the code repeats itself through both sheets at the push of a button. I just use a different code to run the macro through the first sheet, and it then jumps to the second sheet and runs it again. This way I can just edit one simple macro to add any number of sheets I may have to use this on. I can't understand why it will paste the formula into the cell specified on the first sheet, and not on the second. I have tried running it solely on the second sheet with the same results. It selects the correct cells for the formula, then skips over creating the formula and continues with the rest of the macro. I'd post the whole thing but I don't want to clutter the issue with all the extra coding for no reason.

 

[skoolbo]

Situation:

I have to run a report everyday where the numbers in column E through H need to be totalled. I want to write a macro to do this. The problem is that the number of rows in column E through H are different everyday.

Goal:

I would like to have the macro act like the Autosum button in excel, totalling the numbers, in however many rows are in column E through H, and displaying the answer in the cell beneath the last row in columns E through H.

Try this:

Sub Test()
    Dim Rng As Range
    Dim c As Range
    Set Rng = Range("G1:G" & Range("G1").End(xlDown).Row)
    Set c = Range("G1").End(xlDown).Offset(1, 0)
    c.Formula = "=SUM(" & Rng.Address(False, False) & ")"
End Sub

This code worked perfectly for the person who posted originally needing only column G totalled , but I'm trying to autosum Columns E through H. How can I alter this code to achieve this result? Thank you!!!