Excel vba: Getting a sum then using that value to figure percentage

[OALes]

I need to sum a column of numbers that always begins at H10 but the last row will vary based on an unknown number of data entries. After I get the total I need to divide by 51 to get the percentage I need.

I have some code that is working on my MacBook, but when I take it to Windows it isn’t working. I’m sorry, I don’t have that code handy. My MacBook runs Excel 365 and the PC runs Excel 2016.

 

[iggydarsa]

=SUM(INDIRECT("H10:H" &ROWS(A:A)))/51

 

[OALes]

=SUM(INDIRECT("H10:H" &ROWS(A:A)))/51

Thank you for the quick response. Can you explain what row(a:a) does? Will that find the last row with contents after row 10 in column H?

 

[OALes]

Also, that doesn’t look like vba. It looks like a sum in excel?

 

[Scott Huish]

ROWS(A:A) just returns the total number of rows in Column A. Older versions of Excel had 65536 and newer versions have 1048576.

You could replace that with a number larger than you would ever need if you want.
=SUM(H10:H65000)/51

I don't have a Mac but in PC VBA something like this?

Range("J1") = Application.WorksheetFunction.Sum(Range("H10:H" & Rows.Count)) / 51

 

[OALes]

That looks a lot like what I have which is working on my Mac but not at work where I use a PC. It’s a little different so I’ll try this on Sunday when I’m back at work. Thank you for your ideas. I will let you know what I find.

 

[Cubist]

Perhaps.

=SUM(A:A)-SUM(A1:A9)

 

[Cubist]

Forgot the division by 51 above.

=(SUM(A:A)-SUM(A1:A9))/51

 

[Scott Huish]

Forgot the division by 51 above.

=(SUM(A:A)-SUM(A1:A9))/51

I think the OP wants VBA code

 

[OALes]

ROWS(A:A) just returns the total number of rows in Column A. Older versions of Excel had 65536 and newer versions have 1048576.

You could replace that with a number larger than you would ever need if you want.
=SUM(H10:H65000)/51

I don't have a Mac but in PC VBA something like this?

Range("J1") = Application.WorksheetFunction.Sum(Range("H10:H" & Rows.Count)) / 51

This is exactly what I needed! Thank you so much!