Sum with Indirect

[Mr2017]

Good morning!

I would like to modify this formula in a macro so that it always looks at a specific column reference (column K)

ActiveCell = "=SUM('Review Tab'!K:K)"

I've changed it to this:

ActiveCell = "=SUM(INDIRECT(" 'Review Tab'!K:K"))"

However, VBA now thinks that everything after the first single inverted comma is a comment!

So everything from the letter R is now a comment!

And I get a "Run-time error: 1004" saying "Application defined or object defined error."

Does anyone how I can make it ignore the single inverted commas here? They are there because I need to reference another tab.

Thanks in advance.

 

[mohadin]

Hi
Try

ActiveCell.Formula = "=SUM(INDIRECT(""Review Tab!K:k""))"

 

[Mr2017]

Hi Mohadin

When I do that, it gives me a #REF ! error.

Any ideas why?

Thanks in advance.

 

[Peter_SSs]

Try

ActiveCell.Formula = "=SUM(INDIRECT(""'Review Tab'!K:K""))"

BUT, do you really need Excel to be summing 1000000+ cells?

 

[Fluff]

Try

"=SUM(INDIRECT(""'Review Tab'!K:K""))"

 

[mohadin]

Oh
Yes, sorry miss typing

 

[Mr2017]

Awesome!!!

That worked!! Thanks Fluff

 

[Mr2017]

Thanks.

The number of rows in the tab it's referencing changes, depending on the data, hence the reason that the sum looks at the whole column.

 

[Special-K99]

The number of rows in the tab it's referencing changes, depending on the data, hence the reason that the sum looks at the whole column.

...and there's no way to work out the last row you want?

 

[Peter_SSs]

The number of rows in the tab it's referencing changes, depending on the data,

.. but does it ever get anywhere near a million rows? or perhaps even only 100,000 rows?
What is the most number of rows you have had to sum with this formula so far?