Sum with Indirect

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
644
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 
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.
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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....
 
Upvote 0
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. :)
 
Upvote 0
There is a lot going on in the spreadsheet, so I'll consider reducing the range, as you've suggested.

Thanks for the suggestion.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top