Using variables in a macro formula. 2

[Stevo911]

Hi

Selection.FormulaArray = _
"=SUM(IF(SMAST2!R2C92:R10976C92=""A"",1,0))"

The above line comes out of a macro i use to sum the number of products in a list assigned an "A" in the range, (row 2 -> row 10976, column 92).

The formula works well, i got it by using the record mode.

Question:

Lets say 10 more products are added onto the list. Rather than using find and replace to change 10976 to 10986 in the code each time. Is there a way to use a variable for the number of rows and then insert this variable into the formula?

eg.
"=SUM(IF(SMAST2!R2C92:R**"Variable" or "object"**C92=""A"",1,0))"

If this is not possible, is it because the formula is in an excel notation and not strictly VBA?
Do i need to reference the rows and columns differently?

I've tried looking at the, "How can i rename a table as it changes size?" FAQ and it doesnt really help.

Any help would be very much appreciated.
Thanks

 

[fredericofonseca]

If I´m not wrong you can name a range to do that, and then use the range name instead of the range itself.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[PHV]

Anyway take a look in the FAQ area to discover how to retrieve the last used row.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[combo]

The direct answer is: a string is assigned to FormulaArray, if you know row numbers, you can use & operator to build it and execute the macro.

If you work with a plain table and this is a part of bigger problem, you can work with dynamic range. Suppose the table starts in A1 and there are no empty cells in first column, you can define name using =OFFSET(SHEETNAME!$A$1,,,COUNTA(SHEETNAME!$A:$A),TABLEWIDTH). Arter adding a row, the named range will extend automatically, so you do not have to change formula arguments.
Array formulas are slow, so you could consider use of COUNTIF function.

You can also use a pivot table, that allow, among others, use standard database functions, you can put assignment as a page field and filter calculations. It works with dynamic names, the name has to be manually entered as in source data range.

combo