Macro for sum up and sorting

[shawrod]

I am looking for a simple macro that sums up all cells on C and enter result on the last empty cell of C column of the worksheet and same thing with columns D-H. On the last cells of Columns G and H that has the result , set cell format to $. Then I would like to select the whole data on the worksheet except for the last row and sort it by column I from largest to smallest. Can anyone help?

 

[shawrod]

It didn't work. The code stops at the first line " lastrow = Cells(Row.Count, K).End(xlUp).Row "
with "object required" dialog and "debug" and "end" option

 

[Momentman]

Sorry that line should be rows.count rather than row.count. So you have

lastrow = Cells(Rows.Count, K).End(xlUp).Row

 

[shawrod]

This time worked but there is still a problem and I don't think if it does anything with your code. The code applied formatting to all cells from 2-9 one cell above the last cell on A column and I believe the reason is the last cell on column A is blank but the last cells on other columns have values and looks like the code only sees the last cell with value not necessarily the last row. Couple things I would like to ask you if you don't mind, how do I select the last cell on Column A so I can add the word "Total" in there . In that case, all cells on the last row will have value and this code should work. Other thing I want to ask how do I "=Count" all cells on column B from B2 to down and put result on the last cell on column B? I truly appreciate your help.

 

[shawrod]

Don't worry about last cell on column A. I got it solved by using this code: Range("E" & Cells.Rows.Count).End(xlUp).Offset(1,0).Select that stefankemp helped me on another link. I just need to figure how to count all cells from B2 and down and put result on the last non-empty cell on column B.

 

[Momentman]

if you can get the last used cell in column B, that value minus 1 should be the result you want.

countcells = Range("B2").End(xlDown).Row-1

 

[shawrod]

This code didn't do anything. I didn't see any changes!

 

[Momentman]

This code didn't do anything. I didn't see any changes!

Of course, you wont see any changes, it barely assigns the number of cells to a variable called countcells. If you want it displayed use

msgbox countcells

 

[shawrod]

Of course, you wont see any changes, it barely assigns the number of cells to a variable called countcells. If you want it displayed use

msgbox countcells

What I am trying to do is to select B2 all the way down to one cell above last non empty cell and count and put the result in the last non empty cell on B column. Does it make sense?

 

[Momentman]

What I am trying to do is to select B2 all the way down to one cell above last non empty cell and count and put the result in the last non empty cell on B column. Does it make sense?

it makes sense, you could use 2 lines of code like this

Sub Mylast()    
    Range("B2").End(xlDown).Offset(1, 0).Select
    ActiveCell.Value = Range("B2").End(xlDown).Row - 1
End Sub

The first line selects the first empty cell and puts the result in there. Do you really intend to put the result in the last non-empty cell. If its last non-empty cell then this would work

Sub Mylast()    
    Range("B2").End(xlDown).Select
    ActiveCell.Value = Range("B2").End(xlDown).Row - 1
End Sub

 

[shawrod]

Yes that's what I intended. I like your second code but I don't think it is counting correctly. It seems it is counting from B1 to the cell above where the result is. I want to count from B2 to the cell above where the result is. Example:

B1 apple
B2 5
B3 orange
B4 3
B5 xyz
The result should go to B5 and overwrite what it is in there which your code does this part right. The result should be 3 but your code says 4.

it makes sense, you could use 2 lines of code like this

Sub Mylast()    
    Range("B2").End(xlDown).Offset(1, 0).Select
    ActiveCell.Value = Range("B2").End(xlDown).Row - 1
End Sub

The first line selects the first empty cell and puts the result in there. Do you really intend to put the result in the last non-empty cell. If its last non-empty cell then this would work

Sub Mylast()    
    Range("B2").End(xlDown).Select
    ActiveCell.Value = Range("B2").End(xlDown).Row - 1
End Sub