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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Hi
Try

Code:
ActiveCell.Formula = "=SUM(INDIRECT(""Review Tab!K:k""))"
 
Last edited:
Upvote 0
Hi Mohadin

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

Any ideas why?

Thanks in advance.
 
Upvote 0
Try
Code:
ActiveCell.Formula = "=SUM(INDIRECT(""'Review Tab'!K:K""))"

BUT, do you really need Excel to be summing 1000000+ cells?
 
Upvote 0
Try
Code:
"=SUM(INDIRECT(""'Review Tab'!K:K""))"
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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