1

I am trying to add up the numbers that are in a column, but the column of numbers varies so I'm using the Rows.Count instead of a set number of rows such as "a1:a5". Each time I run this I get the row count (represented as rngcount) but instead of the sum of the cells within the row count (rngcount) I get the row count again.

I was getting an object error (1004) until I added "a" to .Range("a" & rngcount) but I admit I'm not sure why the "a" is needed as I thought the rngcountwould be all that is needed to Sum.

So Unfortunately two issues in one post. 

Sub simpleSUM()

Dim rng1 As Range
Dim rng2 As Range
Dim rngcount As Integer

Set rng1 = Range("b1") 'This indicates how many cells are in use
Set rng2 = Range("b2") 'This indicates the sum of the cells that are in use

rngcount = cells(Rows.Count, "A").End(xlUp).row

rng1.Value = rngcount
rng2.Value = Application.WorksheetFunction.Sum(ThisWorkbook.Sheets("sheet2").Range("a" & rngcount))
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
officemanager2
  • 17
  • 5
  • Note that row counting variables **must** be of type `Long` because Excel has more rows than `Integer` can handle: `Dim rngcount As Long`. I recommend [always to use Long instead of Integer](https://stackoverflow.com/a/26409520/3219613) in VBA since there is no benefit in `Integer` at all. – Pᴇʜ Feb 25 '19 at 09:10

1 Answers1

1

Your code now only counts the last row. The variable rngcount returns the last rownumber in the range, instead of the whole range.

It should work if you use

rng2.Value = Application.WorksheetFunction.Sum(Thisworkbook.Sheets("sheet2").Range("A1:A" & rngcount))
Alex de Jong
  • 1,257
  • 1
  • 11
  • 23
  • Yes. Might also be worth noting that best practice is to declare `rngcount As Long` instead of `As Integer`. – BigBen Feb 23 '19 at 15:57
  • Thanks, I'll click the check as soon as the time limit allows. I do have a follow up question which is why is "a1;a" needed? My thinking up until this point is that the range is defined in the variable "rngcount" so I'm not sure what I'm missing. Without "a1:a" I know there is no object found, but I go back to my initial thinking that the object is defined earlier as the integer 'rngcount' ... I am trying to mark up my comment correctly but I cannot find ` on my keyboard – officemanager2 Feb 23 '19 at 16:07
  • The range is identified by a string, such as "A1:A5". The rngcount variable is only a number. So to complete the range, you combine the "A1:A" part with the number from rngcount to get a complete string that can be identified as a range. – Alex de Jong Feb 23 '19 at 16:11
  • Fair enough, if you have time for one more question, why the need for "a1:a" instead of just "a". I could be wrong (probably I am) but by declaring "a" does that not capture the entire range of column a. That will be my last question on this, thank you for your time. – officemanager2 Feb 23 '19 at 16:18
  • @officemanager2 if it helps, say the last row is 200. The revised line of code is equivalent to the formula `=SUM(A1:A200)`. Your original code would be equivalent to `=SUM(A200)`, which just takes the last cell into account. – BigBen Feb 23 '19 at 16:24
  • No, because let's say rngcount = 5, then "a" & rngcount will result in "A5", which points to the cell A5. The entire column is identified by "A:A", but since you want to stop at the last row, say 5, you also need to specify the start of the range. "A:A5" for instance would cause an error. – Alex de Jong Feb 23 '19 at 16:27