Generic row reference in formula

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

I am using a formula in Excel to sum the values in a row:

=SUM(B2:F2)

Is there a way to use a generic row reference in a formula? For
example, I don't know for sure at run time if the data and formula is
going to end up in the second row. What I want is something like this

=SUM(B#:F#)

where the # sign would indicate the current row containing the
formula.

Thank you very much.

Rick Quatro
(e-mail address removed)
 
I don't understand.

If you're writing the formula manually, you know what row you're in.

If you're writing the formula through code, you could use .formulaR1C1:

with worksheets("somesheetnamehere")
.cells(somerownumber,"A").formular1c1 = "=sum(rc2:rc6)"
end with

RC2 means same row, column 2 (B)
RC6 means same row, column 6 (F)

or you could still use .formula:
dim oRow as long

orow = 32342 'whatever row you need.
with worksheets("somesheetnamehere")
.cells(orow,"A").formula = "=sum(B" & orow & ":F" & orow & ")"
end with
 
This works fine, but there is one thing that would make it better for
me. Is there a way to use a numeric reference to the column instead of
using the letters? For example, instead of using B, can I use 2;
instead of F, can I use 6? Thank you very much.

Rick
 
Hi Rick
Try
=SUM(INDEX($1:$65536,ROW(),2):INDEX($1:$65536,ROW(),6))

--
Regards

Roger Govier


Rick said:
This works fine, but there is one thing that would make it better for
me. Is there a way to use a numeric reference to the column instead of
using the letters? For example, instead of using B, can I use 2;
instead of F, can I use 6? Thank you very much.

Rick
Click to expand...
 
Back
Top