SUM of cells from (x1,y1) to (x2,y2) where x,y - numbers

  • Thread starter Thread starter qbek3
  • Start date Start date
Q

qbek3

How to write formula to sum range of cells, when i know numeri
coordinates of first and last cell?

I tried to convert numeric coordinates to excel format with ADDRES
with something like SUM(ADDRESS(x1,y1):ADDRESS(x2y2)), but ADDRES
output is text, so i got error from sum
 
Thanks. INDIRECT was the key. I have another problem However. These SU
formulas are inserted by VBA code. When I open generated file al
formulas return name error. When I edit formula then exit (double clic
then enter) cell I get my sum.
 
qbek3 wrote...
Thanks. INDIRECT was the key. I have another problem
However. These SUM formulas are inserted by VBA code. When I
open generated file all formulas return name error. When I edit
formula then exit (double click then enter) cell I get my sum.
Click to expand...

Don't screw around with INDIRECT when using macros to enter formulas
Also, give *FULL* details of what you're doing in your initia
posting.

In VBA, if you know x1, y1, x2 and y2, and ws is a Worksheet objec
referring to the worksheet in which you're entering these formulas, us
something like

ws.Range("X99").Formula = "=SUM(" & _
ws.Cells(x1, y1).Address(0, 0, xlA1) & ":" & _
ws.Cells(x2, y2).Address(0, 0, xlA1) & ")"

If x1 = 5, y1 = 12, x2 = 100, y2 = 20, the statement above would ente
the following formula into cell X99.

=SUM(L5:T100
 
What's wrong with indirect in macro generated formulas?
Why I got errors? Why editing without changing anything corrected them


I scan values of cells of one of the columns and insert rows wit
subtotals when value changes. I have an array a of column numbers wher
i put sum formulas j and i are numbers of first and last rows to sum
Current working formula below:

xlsh.Rows(i).Insert
For n = 1 To 26
xlsh.Cells(i+1, a(n)).Formula = "=SUM(" & _
xlsh.Cells(j, a(n)).Address(0, 0, xlA1) & ":" & _
xlsh.Cells(i, a(n)).Address(0, 0, xlA1) & ")"
Next n

Your solution with cells().address works great. Thank you.
 
qbek3 wrote...
What's wrong with indirect in macro generated formulas?
Click to expand...

If the x and y values are VBA variables, VBA would generate formulas
that looked like

=SUM(INDIRECT("R5C3:R20:C6",0))

The first argument to INDIRECT would be a constant in the resulting
formula. There are some situations in which this could be advantageous,
but not in your situation as you've described it.

Better to have VBA enter the formula as

=SUM(C5:F20)
Why I got errors? Why editing without changing anything
corrected them?
Click to expand...
...

Unknown without seeing your VBA code generating formulas calling
INDIRECT.
 
Back
Top