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.

 

[Mr2017]

There is, but that would require some changes to a worksheet that's already quite complicated! There's over 100 lines of code. So I want to keep this part simple, for now. I may change it in future, though.

 

[Mr2017]

The max may be 10,000.

Will it make that much of a difference if I change it to 100,000 rows, though? The code only adds three formulas which sum up data in a different tab....

 

[Peter_SSs]

The max may be 10,000.

Will it make that much of a difference if I change it to 100,000 rows, though? The code only adds three formulas which sum up data in a different tab....

If the max is about 10,000 then you could easily change the range to, say, 50,000.

However, if you don't have a lot going on in your workbook then, no, it wouldn't make a lot of difference.

 

[Mr2017]

There is a lot going on in the spreadsheet, so I'll consider reducing the range, as you've suggested.

Thanks for the suggestion.