Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime Error '91' Object variable or With block variable not set | Excel Discussion (Misc queries) | |||
Inserting and retaining an input variable | Excel Discussion (Misc queries) | |||
Sum cells based on a row variable and seperate column variable | Excel Worksheet Functions | |||
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
reading/inserting variable dates within a macro | Excel Programming |