I have a macro that inserts a SUM formula for a range of cells on a single
row. Here's what I have so far:

Dim RegionRange as String

RegionRange = Range(Cells(NextBlankRow, FIRSTREGION_COL),
Cells(NextBlankRow, TotalCol - 1)).Address
Worksheets(DATASHEET).Cells(NextBlankRow, TotalCol).Formula = "=Sum(" &
RegionRange & ")"

The value of variable RegionRange ends up being something like
"$B$49:$H$49", which is correct but I don't want an absolute reference.
Everything works fine, but I would prefer to have a relative reference like
"B49:H49" instead. There's the possibility that the user could insert a
column in which case the SUM using the absolute reference won't pick up the
new data.

Thanks,

Barry

RegionRange = Range(Cells(NextBlankRow, FIRSTREGION_COL),
Cells(NextBlankRow, TotalCol - 1)).Address(False,False)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"BCS" wrote in message
m...
I have a macro that inserts a SUM formula for a range of cells on a single
row. Here's what I have so far:

Dim RegionRange as String

RegionRange = Range(Cells(NextBlankRow, FIRSTREGION_COL),
Cells(NextBlankRow, TotalCol - 1)).Address
Worksheets(DATASHEET).Cells(NextBlankRow, TotalCol).Formula = "=Sum(" &
RegionRange & ")"

The value of variable RegionRange ends up being something like
"$B$49:$H$49", which is correct but I don't want an absolute reference.
Everything works fine, but I would prefer to have a relative reference
like
"B49:H49" instead. There's the possibility that the user could insert a
column in which case the SUM using the absolute reference won't pick up
the
new data.

Thanks,

Barry