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


--
pwermuth
------------------------------------------------------------------------
pwermuth's Profile: http://www.excelforum.com/member.php...o&userid=24997
View this thread: http://www.excelforum.com/showthread...hreadid=385424

referencing ranges using R1C1 format
 

this clears a1 in excel 2k3

Cells(1, 1).ClearContents

this clears a1:b2

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

--


Gary


"pwermuth" wrote in
message ...

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

referencing ranges using R1C1 format
 

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

referencing ranges using R1C1 format
 

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

referencing ranges using R1C1 format
 

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

referencing ranges using R1C1 format
 

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

referencing ranges using R1C1 format
 

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