Sum with Indirect

Mr2017

Active Member
Joined
Nov 28, 2016
Messages
459
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.
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
Hi
Try

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

Mr2017

Active Member
Joined
Nov 28, 2016
Messages
459
Hi Mohadin

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

Any ideas why?

Thanks in advance.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,401
Office Version
365
Platform
Windows
Try
Code:
ActiveCell.Formula = "=SUM(INDIRECT(""'Review Tab'!K:K""))"
BUT, do you really need Excel to be summing 1000000+ cells?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,439
Office Version
365
Platform
Windows
Try
Code:
"=SUM(INDIRECT(""'Review Tab'!K:K""))"
 

Mr2017

Active Member
Joined
Nov 28, 2016
Messages
459
Awesome!!!

That worked!! Thanks Fluff
 

Mr2017

Active Member
Joined
Nov 28, 2016
Messages
459
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

Well-known Member
Joined
Nov 7, 2006
Messages
8,341
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,401
Office Version
365
Platform
Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,177
Messages
5,442,840
Members
405,201
Latest member
kashyap44

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top