Need to sum up Columns A3 to to A30 and then add total in B32

[Rubicon9]

I'm trying to figure out how to total Columns from the cell range of A3 to the whenever, I have 51 sheets and each sheet has different cells populated.
Example:
Sheet1 - might have 8 populated cells from A3 to A8 - I need the total populated in B12.
Sheet2 - might have 370 populated cells from A3 to A273 - I need the total populated in B375.

Current Code and it does not work:
It produces =A6 in row 23 column A

Dim LR As Long
With Sheets("Sheet1")
LR = .Range("A" & Rows.Count).End(xlUp).Row
Range("A3" & LR).Formula = "=A6"
End With


Thanks,
Rubi

[vcoolio]

Hello Rubi,

Taking your first example:

Sheet1 - might have 8 populated cells from A3 to A8 - I need the total populated in B12.

You could try either of the following:-

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

within your code.

The second option will define the actual used cells whereas the first option just checks the whole column.

I hope that this helps.

Cheerio,
vcoolio.

[AlphaFrog]

Try something like this...

Please Login or Register  to view this content.

[Rubicon9]

Thanks AlphaFrog..All I get back is =SUM(R3C1:R[-2]C1) in the cell.

[Rubicon9]

Thanks vcoolio ......It brings back a value of "0" in B12.

Looking for the cell count from what ever is in A3 to whatever cell is populated after A2.

[AlphaFrog]

Thanks AlphaFrog..All I get back is =SUM(R3C1:R[-2]C1) in the cell.

It works for me. Is column B formatted as text? If yes, change the column format or try this...

Please Login or Register  to view this content.

[Rubicon9]

It works for me. Is column B formatted as text? If yes, change the column format or try this...

Please Login or Register  to view this content.

Getting closer...now it populates with '0'...doesn't count the cells .

[vcoolio]

@AlphaFrog,

I would say, at a stab, that Column A is actual text. Rubi may be needing COUNTIFS.
Anyway, I'll leave it with you. I'm going to bed!

Goodnight people.

vcoolio.

[AlphaFrog]

Then column A is formatted as text as well. Try this...

Please Login or Register  to view this content.

[Rubicon9]

@AlphaFrog,

I would say, at a stab, that Column A is actual text. Rubi may be needing COUNTIFS.
Anyway, I'll leave it with you. I'm going to bed!

Goodnight people.

vcoolio.

Column A has text in A1 and A2...A3 down only has Facility ID's that we use. Such as A3=072.... I just need to know how many facilities there are in each of the rows in that column of A3 to lets just say 1000.....Sorry for the confusion.

Rubi

[Rubicon9]

Then column A is formatted as text as well. Try this...

Please Login or Register  to view this content.

Column A has text in A1 and A2...A3 down only has Facility ID's that we use. Such as A3=072.... I just need to know how many facilities there are in each of the rows in that column of A3 to lets just say 1000.....Sorry for the confusion.

Rubi

[AlphaFrog]

You just want to count the number of entries in column A starting at A3 and not SUM the values?

[Rubicon9]

You just want to count the number of entries in column A starting at A3 and not SUM the values?

Exactly. I apologize for the confusion, I'm new at this.

Rubi

[bakerman2]

If there are no empty rows in between.

Please Login or Register  to view this content.

[AlphaFrog]

Please Login or Register  to view this content.

[Rubicon9]

Please Login or Register  to view this content.

Hey there AlhaFrog--- That Worked!!!!!...Now I can't figure out how to put that value in H2.

Rubi

[AlphaFrog]

Replace this...
.Value = .Row - 4
With this...
Range("H2").Value = .Row - 4