Range using Cell row and column #s

[mangoel]

Hello All,

I am trying to create a string "RangeStr" referencing a range specified only in terms of row and column #s.
ie. Given that A1 = Cells(1,1)
and A10 = Cells(10,1)

I want RangeStr = "A1:A10"

I tried
Dim RangeStr As String
RangeStr = CStr(Range((Cells(1,1), Cells(10,1)))

But I get a type mismatch error

I eventually want to use RangeStr to write a formula into another cell
Cells(rowno,1).Formula = "=sum(" & RangeStr & ")"

I'd really appreciate any help on this. Thanks!

Manish

[ This Message was edited by: mangoel on 2002-05-21 15:37 ]

[Russell Hauf]

You're almost there.

Try this:

RangeStr = Range((Cells(1,1), Cells(10,1)).Address(False, False)

HTH,

Russell

[mangoel]

Russell,

Thank you very much! That worked perfectly.
BTW what does the .Address(False,False) do?

Regards,

Manish

[Russell Hauf]

On 2002-05-21 16:23, mangoel wrote:
Russell,

Thank you very much! That worked perfectly.
BTW what does the .Address(False,False) do?

Regards,

Manish

The (False, False) gives relative references (RowAbsolute:=False, ColumnAbsolute:=False).

If you left them out, you would get "$A$1:$A$10" -- which would also work in your sum formula, but would not if you wanted to drag (fill) the formula to other cells.

Glad I could help,

Russell