Syntax?? - Use VBA to insert an Indirect function

  • Thread starter Thread starter Valencia Jack
  • Start date Start date
V

Valencia Jack

Thanks to this and other forums, I've learned a little about th
Indirect function (INDIRECTO here in Spain, just as SUM is SUMA). But
can't find a way to insert one in an Excel sheet with VBA, because o
the quotes("), I suppose.
Example:
I can insert manually =SUMA(INDIRECTO("C"&D1&":C"&D2)) in
spreadsheet. D1 and D2 hold the beginning and ending rows of the rang
- determined with the program - to be summed.
I've tried...

ActiveCell.FormulaR1C1 ="=SUMA(INDIRECTO("C"&D1&":C"&D2))"
...which doesn't fly.

Can anyone help with an explanation or workaround?

Thanks - BTW, It's my first request to this forum
 
Just like quotation makrs within XL strings, quotation marks within VBA
Strings need to be doubled. Try:

ActiveCell.Formula ="=SUMA(INDIRECTO(""C""&D1&"":C""&D2))"

Note that you don't want to use FormulaR1C1 if you're using A1-type
references.

You could use .FormulaR1C1 with

ActiveCell.FormulaR1C1 = "=SUMA(INDIRECTO(""C""&R1C4&"":C""&R2C4))"
 
Jack,

Two things

The syntax for quotes is double-quotes, that is

"=SUM(INDIRECT(""C""&D1&"":C""&D2))"

But also, this is not an R1C1 formula, so use

ActiveCell.Formula = "=SUM(INDIRECT(""C""&D1&"":C""&D2))"

If you use FormulaR1C1 it puts single quotes around the D1 and D2 and fails
the formula for that.


---
HTH


Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks Bob , Thanks Bob

Of course you are right and I'm glad my workbook works correctly now.

I would have responded sooner, but didn't see your help until just now
 
Back
Top