Thread: referencing ranges using R1C1 format

Hello,

this might be a very basic problem but I cannot find the solution to it in Excel Help.

There are many instances when I need to use Range instead of Cell to refer to a cell. For example methods such as ClearContents only work on Range not on Cell.
However, it seems as if Range only takes the A1 format which drives me crazy. Most of the time I am looping through rows and columns and therefore will have their integer values, i.e. the location of a cell or array in R1C1 format but not in the A1 format.

How can I refer to cell R5C3 for example using Range? Supposedly there is a second syntax for Range which should work as follows Range(Cells(5,3),Cells(5,3)) but whenever I try to use that I get runtime errors. Am I getting this wrong and is there another, better method to quickly convert a simply R1C1 format address into A1 format or to use Range with the R1C1 format?

Thanks for your help,

Peter

this clears a1 in excel 2k3

Cells(1, 1).ClearContents

this clears a1:b2

Range(Cells(1, 1), Cells(2, 2)).ClearContents

--


Gary


"pwermuth" <pwermuth.1rtqmx_1120781133.0799@excelforum-nospam.com> wrote in
message news:pwermuth.1rtqmx_1120781133.0799@excelforum-nospam.com...
>
> Hello,
>
> this might be a very basic problem but I cannot find the solution to it
> in Excel Help.
>
> There are many instances when I need to use Range instead of Cell to
> refer to a cell. For example methods such as ClearContents only work
> on Range not on Cell.
> However, it seems as if Range only takes the A1 format which drives me
> crazy. Most of the time I am looping through rows and columns and
> therefore will have their integer values, i.e. the location of a cell
> or array in R1C1 format but not in the A1 format.
>
> How can I refer to cell R5C3 for example using Range? Supposedly there
> is a second syntax for Range which should work as follows
> Range(Cells(5,3),Cells(5,3)) but whenever I try to use that I get
> runtime errors. Am I getting this wrong and is there another, better
> method to quickly convert a simply R1C1 format address into A1 format
> or to use Range with the R1C1 format?
>
> Thanks for your help,
>
> Peter
>
>
> --
> pwermuth
> ------------------------------------------------------------------------
> pwermuth's Profile:
> http://www.excelforum.com/member.php...o&userid=24997
> View this thread: http://www.excelforum.com/showthread...hreadid=385424
>

Thank you, but the

Range(Cells(1,1),Cells(2,2)) syntax never works for me. I always get runtime errors. Are there some limitations I need to be aware of? For example, can I not refer to a single cell (i.e. Range(Cells(2,2), Cells(2,2)) ) using this syntax?

The only trouble I've had with that syntax is when I don't qualify the ranges.

I like:

dim rng as range
with activesheet 'worksheets("sheet1")
set rng = .range(.cells(1,1),.cells(2,2))
end with

And I could refer to a single cell using your example--but I wouldn't.



pwermuth wrote:
>
> Thank you, but the
>
> Range(Cells(1,1),Cells(2,2)) syntax never works for me. I always get
> runtime errors. Are there some limitations I need to be aware of? For
> example, can I not refer to a single cell (i.e. Range(Cells(2,2),
> Cells(2,2)) ) using this syntax?
>
> --
> pwermuth
> ------------------------------------------------------------------------
> pwermuth's Profile: http://www.excelforum.com/member.php...o&userid=24997
> View this thread: http://www.excelforum.com/showthread...hreadid=385424

--

Dave Peterson

Dave Peterson wrote:
> The only trouble I've had with that syntax is when I don't qualify the ranges.

One can avoid the qualification problem with

Set baseRng = ActiveSheet.Range("A1")
Set rng=Range(baseRng(1,1),baseRng(2,2))

You can refer to a single cell with, e.g., baseRng(2,2)

Alan Beban
>
> I like:
>
> dim rng as range
> with activesheet 'worksheets("sheet1")
> set rng = .range(.cells(1,1),.cells(2,2))
> end with
>
> And I could refer to a single cell using your example--but I wouldn't.
>
>
>
> pwermuth wrote:
>
>>Thank you, but the
>>
>>Range(Cells(1,1),Cells(2,2)) syntax never works for me. I always get
>>runtime errors. Are there some limitations I need to be aware of? For
>>example, can I not refer to a single cell (i.e. Range(Cells(2,2),
>>Cells(2,2)) ) using this syntax?
>>
>>--
>>pwermuth
>>------------------------------------------------------------------------
>>pwermuth's Profile: http://www.excelforum.com/member.php...o&userid=24997
>>View this thread: http://www.excelforum.com/showthread...hreadid=385424
>
>

There are even potential problems with this--depending on where the code is:

I put this in the module for Sheet1:

Option Explicit
Sub testme()
Dim baseRng As Range
Dim rng As Range
Set baseRng = Worksheets("sheet2").Range("A1")
Set rng = Range(baseRng(1, 1), baseRng(2, 2))
End Sub

I got an error with that last "set rng = Range(baseRng(1, 1), baseRng(2, 2))"
statement.

If I changed it to:
Set rng = Application.Range(baseRng(1, 1), baseRng(2, 2))

It worked fine.

I bet it's because that unqualified range() is still looking at the sheet that
owns the code.





Alan Beban wrote:
>
> Dave Peterson wrote:
> > The only trouble I've had with that syntax is when I don't qualify the ranges.
>
> One can avoid the qualification problem with
>
> Set baseRng = ActiveSheet.Range("A1")
> Set rng=Range(baseRng(1,1),baseRng(2,2))
>
> You can refer to a single cell with, e.g., baseRng(2,2)
>
> Alan Beban
> >
> > I like:
> >
> > dim rng as range
> > with activesheet 'worksheets("sheet1")
> > set rng = .range(.cells(1,1),.cells(2,2))
> > end with
> >
> > And I could refer to a single cell using your example--but I wouldn't.
> >
> >
> >
> > pwermuth wrote:
> >
> >>Thank you, but the
> >>
> >>Range(Cells(1,1),Cells(2,2)) syntax never works for me. I always get
> >>runtime errors. Are there some limitations I need to be aware of? For
> >>example, can I not refer to a single cell (i.e. Range(Cells(2,2),
> >>Cells(2,2)) ) using this syntax?
> >>
> >>--
> >>pwermuth
> >>------------------------------------------------------------------------
> >>pwermuth's Profile: http://www.excelforum.com/member.php...o&userid=24997
> >>View this thread: http://www.excelforum.com/showthread...hreadid=385424
> >
> >

--

Dave Peterson

That's what it seems; the following works in the module for Sheet1:

Sub testme3()
Dim baseRng As Range
Dim rng As Range
Set baseRng = Worksheets("Sheet2").Range("A1")
Set rng = baseRng(2, 2)
MsgBox rng.Parent.Name & " " & rng.Address '<---Displays Sheet2 $B$2
End Sub

Alan Beban

Dave Peterson wrote:
> There are even potential problems with this--depending on where the code is:
>
> I put this in the module for Sheet1:
>
> Option Explicit
> Sub testme()
> Dim baseRng As Range
> Dim rng As Range
> Set baseRng = Worksheets("sheet2").Range("A1")
> Set rng = Range(baseRng(1, 1), baseRng(2, 2))
> End Sub
>
> I got an error with that last "set rng = Range(baseRng(1, 1), baseRng(2, 2))"
> statement.
>
> If I changed it to:
> Set rng = Application.Range(baseRng(1, 1), baseRng(2, 2))
>
> It worked fine.
>
> I bet it's because that unqualified range() is still looking at the sheet that
> owns the code.
>
>
>
>
>
> Alan Beban wrote:
>
>>Dave Peterson wrote:
>>
>>>The only trouble I've had with that syntax is when I don't qualify the ranges.
>>
>>One can avoid the qualification problem with
>>
>>Set baseRng = ActiveSheet.Range("A1")
>>Set rng=Range(baseRng(1,1),baseRng(2,2))
>>
>>You can refer to a single cell with, e.g., baseRng(2,2)
>>
>>Alan Beban
>>
>>>I like:
>>>
>>> dim rng as range
>>> with activesheet 'worksheets("sheet1")
>>> set rng = .range(.cells(1,1),.cells(2,2))
>>> end with
>>>
>>>And I could refer to a single cell using your example--but I wouldn't.
>>>
>>>
>>>
>>>pwermuth wrote:
>>>
>>>
>>>>Thank you, but the
>>>>
>>>>Range(Cells(1,1),Cells(2,2)) syntax never works for me. I always get
>>>>runtime errors. Are there some limitations I need to be aware of? For
>>>>example, can I not refer to a single cell (i.e. Range(Cells(2,2),
>>>>Cells(2,2)) ) using this syntax?
>>>>
>>>>--
>>>>pwermuth
>>>>------------------------------------------------------------------------
>>>>pwermuth's Profile: http://www.excelforum.com/member.php...o&userid=24997
>>>>View this thread: http://www.excelforum.com/showthread...hreadid=385424
>>>
>>>
>