inserting a variable into VB

  • Thread starter Thread starter edb
  • Start date Start date
E

edb

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
 
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)
 
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.
 
Back
Top