inserting a variable into VB
 

Hi, I am trying to use a variable (rowNo), which is an integer an
refers to a row number, in this statement:

Range("n5").FormulaArray = "=SUM(J12:J"&rowNo&")"

so that the range is from cell J12 down to JrowNo (what ever rowNo i
it will be greater than 12).

I cannot figure out how to get this to work...
Is this even possible?
Thanks and regards
E

--
Message posted from http://www.ExcelForum.com

inserting a variable into VB
 

Ed,

That code works perfectly well as long as you add spaces around the &.
However, SUM works on arrays, so you don't need an array formula, this works
just as well

Range("N5").Formula = "=SUM(J12:J" & rowNo & ")"


--

HTH

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

"edb " wrote in message
...
Hi, I am trying to use a variable (rowNo), which is an integer and
refers to a row number, in this statement:

Range("n5").FormulaArray = "=SUM(J12:J"&rowNo&")"

so that the range is from cell J12 down to JrowNo (what ever rowNo is
it will be greater than 12).

I cannot figure out how to get this to work...
Is this even possible?
Thanks and regards
Ed


---
Message posted from http://www.ExcelForum.com/

inserting a variable into VB
 

Hi Ed

It's an ordinary formula, so

Range("n5").Formula = "=SUM(J12:J" & rowNo & ")"

will do, however notice, that you have to put
spaces around the &'s. If you don't Excel may
"think", that & is the typedeclaration for the
datatype Long, and return an error.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"edb " skrev i en meddelelse
...
Hi, I am trying to use a variable (rowNo), which is an integer and
refers to a row number, in this statement:

Range("n5").FormulaArray = "=SUM(J12:J"&rowNo&")"

so that the range is from cell J12 down to JrowNo (what ever rowNo is
it will be greater than 12).

I cannot figure out how to get this to work...
Is this even possible?
Thanks and regards
Ed


---
Message posted from http://www.ExcelForum.com/