refer to a particular cell in r1c1 style

[John Smith]

How do I refer to a particular cell when the spreadsheet is set to
r1c1 style? The spreadsheet does not accept H5 or things like
that. Thanks.

 

[Niek Otten]

You can learn quickly by clicking cells instead of typing their addresses.
Or temporarily change to A1 style (Tools>Options>General tab, Uncheck R1C1 Reference style) and then change back (or not).
If the formula is in A1, then =H5 becomes =R[4]C[7]
$A$1 becomes R5C8

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| How do I refer to a particular cell when the spreadsheet is set to
| r1c1 style? The spreadsheet does not accept H5 or things like
| that. Thanks.

 

[Dave Peterson]

You should be able to refer to a range that way--no matter what the display is
set for.

activesheet.range("H5").value = "hi there!"

works ok for me.

You may want to post what didn't work?

 

[John Smith]

You should be able to refer to a range that way--no matter what the display is
set for.

activesheet.range("H5").value = "hi there!"

works ok for me.

You may want to post what didn't work?

I have the following formula in cells and they all show "#name?".

=min(h4,r[-1]c[4])

The way I got around it (in the old macro xlm file) was to set a
name, say TOMATOPRICE, to h4. Then I could use the formula:

=min(TOMATOPRICE,r[-1]c[4])

Don't know how to do that in vba.

 

[Dave Peterson]

You can't mix R1C1 reference style with A1 reference style.

=min(h4,r[-1]c[4])
could be
=min(r4c8,r[-1]c[4])

Depending on what cell is getting the formula, you could use A1 reference style
in both spots.

You should be able to refer to a range that way--no matter what the display is
set for.

activesheet.range("H5").value = "hi there!"

works ok for me.

You may want to post what didn't work?

I have the following formula in cells and they all show "#name?".

=min(h4,r[-1]c[4])

The way I got around it (in the old macro xlm file) was to set a
name, say TOMATOPRICE, to h4. Then I could use the formula:

=min(TOMATOPRICE,r[-1]c[4])

Don't know how to do that in vba.

 

[Niek Otten]

OK, 3rd time I try:



Looks like my response from a few hours ago has lost its way

====================

You can learn quickly by clicking cells instead of typing their addresses.

Or temporarily change to A1 style (Tools>Options>General tab, Uncheck R1C1 Reference style) and then change back (or not).

If the formula is in A1, then =H5 becomes =R[4]C[7]

$A$1 becomes R5C8



--

Kind regards,



Niek Otten

Microsoft MVP - Excel

====================



| How do I refer to a particular cell when the spreadsheet is set to
| r1c1 style? The spreadsheet does not accept H5 or things like
| that. Thanks.

 

[John Smith]

The problem is those formula were generated by a script using
range.formula command. What I need is a way to refer to a
particular cell while in R1C1 style and that is my original
question (post again in the following).

How do I refer to a particular cell when the spreadsheet is set to
r1c1 style? The spreadsheet does not accept H5 or things like
that. Thanks.

You can't mix R1C1 reference style with A1 reference style.

=min(h4,r[-1]c[4])
could be
=min(r4c8,r[-1]c[4])

Depending on what cell is getting the formula, you could use A1 reference style
in both spots.

You should be able to refer to a range that way--no matter what the display is
set for.

activesheet.range("H5").value = "hi there!"

works ok for me.

You may want to post what didn't work?


John Smith wrote:


How do I refer to a particular cell when the spreadsheet is set to
r1c1 style? The spreadsheet does not accept H5 or things like
that. Thanks.

I have the following formula in cells and they all show "#name?".

=min(h4,r[-1]c[4])

The way I got around it (in the old macro xlm file) was to set a
name, say TOMATOPRICE, to h4. Then I could use the formula:

=min(TOMATOPRICE,r[-1]c[4])

Don't know how to do that in vba.

 

[Dave Peterson]

What is the address that gets the formula?

Since your R1C1 reference style formula uses relative rows/columns, you'll have
to share the cell's address that would get the formula.

The problem is those formula were generated by a script using
range.formula command. What I need is a way to refer to a
particular cell while in R1C1 style and that is my original
question (post again in the following).

How do I refer to a particular cell when the spreadsheet is set to
r1c1 style? The spreadsheet does not accept H5 or things like
that. Thanks.

You can't mix R1C1 reference style with A1 reference style.

=min(h4,r[-1]c[4])
could be
=min(r4c8,r[-1]c[4])

Depending on what cell is getting the formula, you could use A1 reference style
in both spots.

Dave Peterson wrote:

You should be able to refer to a range that way--no matter what the display is
set for.

activesheet.range("H5").value = "hi there!"

works ok for me.

You may want to post what didn't work?


John Smith wrote:


How do I refer to a particular cell when the spreadsheet is set to
r1c1 style? The spreadsheet does not accept H5 or things like
that. Thanks.


I have the following formula in cells and they all show "#name?".

=min(h4,r[-1]c[4])

The way I got around it (in the old macro xlm file) was to set a
name, say TOMATOPRICE, to h4. Then I could use the formula:

=min(TOMATOPRICE,r[-1]c[4])

Don't know how to do that in vba.

 

[John Smith]

Sorry. I just realized that I did not explain my problem properly.
What I want to do is this:

range.formula = "=min(H4,R[-1]C[4])" 'Of course, I cannot use H4 here.

I want a series of cells all compare the value in a relative cell
with that in an absolute cell. One example would be like this:

A5: =min(H4,E4)
A6: =min(H4,E5)
A7: =min(H4,E6)
A8: =min(H4,E7)

And I need to use a script (range.formula comes into my mind) to
fill in these formulae. Since I cannot use the script

range.formula = "=min(H4,R[-1]C[4])"

What do I do to replace H4 with something that works?

What is the address that gets the formula?

Since your R1C1 reference style formula uses relative rows/columns, you'll have
to share the cell's address that would get the formula.

The problem is those formula were generated by a script using
range.formula command. What I need is a way to refer to a
particular cell while in R1C1 style and that is my original
question (post again in the following).

How do I refer to a particular cell when the spreadsheet is set to
r1c1 style? The spreadsheet does not accept H5 or things like
that. Thanks.

You can't mix R1C1 reference style with A1 reference style.

=min(h4,r[-1]c[4])
could be
=min(r4c8,r[-1]c[4])

Depending on what cell is getting the formula, you could use A1 reference style
in both spots.




John Smith wrote:


Dave Peterson wrote:


You should be able to refer to a range that way--no matter what the display is
set for.

activesheet.range("H5").value = "hi there!"

works ok for me.

You may want to post what didn't work?


John Smith wrote:



How do I refer to a particular cell when the spreadsheet is set to
r1c1 style? The spreadsheet does not accept H5 or things like
that. Thanks.


I have the following formula in cells and they all show "#name?".

=min(h4,r[-1]c[4])

The way I got around it (in the old macro xlm file) was to set a
name, say TOMATOPRICE, to h4. Then I could use the formula:

=min(TOMATOPRICE,r[-1]c[4])

Don't know how to do that in vba.

 

[Dave Peterson]

You could use R1C1 reference style:

With activesheet.range("A5:A8")
.formular1c1 = "=min(r4c8,r[-1]c[4])"
end with

or using A1 Reference style:

With activesheet.range("A5:A8")
.formula = "=min($h$4,E4)"
end with

Excel/vba will adjust the formula for each cell.

Just like when you select A5:A8 and type this formula:
=min($h$4,e4)
and hit ctrl-enter to fill all the selected cells.

Just make sure you write the formula for the top cell.

Sorry. I just realized that I did not explain my problem properly.
What I want to do is this:

range.formula = "=min(H4,R[-1]C[4])" 'Of course, I cannot use H4 here.

I want a series of cells all compare the value in a relative cell
with that in an absolute cell. One example would be like this:

A5: =min(H4,E4)
A6: =min(H4,E5)
A7: =min(H4,E6)
A8: =min(H4,E7)

And I need to use a script (range.formula comes into my mind) to
fill in these formulae. Since I cannot use the script

range.formula = "=min(H4,R[-1]C[4])"

What do I do to replace H4 with something that works?

What is the address that gets the formula?

Since your R1C1 reference style formula uses relative rows/columns, you'll have
to share the cell's address that would get the formula.

The problem is those formula were generated by a script using
range.formula command. What I need is a way to refer to a
particular cell while in R1C1 style and that is my original
question (post again in the following).

How do I refer to a particular cell when the spreadsheet is set to
r1c1 style? The spreadsheet does not accept H5 or things like
that. Thanks.

Dave Peterson wrote:

You can't mix R1C1 reference style with A1 reference style.

=min(h4,r[-1]c[4])
could be
=min(r4c8,r[-1]c[4])

Depending on what cell is getting the formula, you could use A1 reference style
in both spots.




John Smith wrote:


Dave Peterson wrote:


You should be able to refer to a range that way--no matter what the display is
set for.

activesheet.range("H5").value = "hi there!"

works ok for me.

You may want to post what didn't work?


John Smith wrote:



How do I refer to a particular cell when the spreadsheet is set to
r1c1 style? The spreadsheet does not accept H5 or things like
that. Thanks.


I have the following formula in cells and they all show "#name?".

=min(h4,r[-1]c[4])

The way I got around it (in the old macro xlm file) was to set a
name, say TOMATOPRICE, to h4. Then I could use the formula:

=min(TOMATOPRICE,r[-1]c[4])

Don't know how to do that in vba.

 

[Tom Ogilvy]

range.formulaR1C1 = "=min(R4C8,R[-1]C[4])"

--
Regards,
Tom Ogilvy

Sorry. I just realized that I did not explain my problem properly.
What I want to do is this:

range.formula = "=min(H4,R[-1]C[4])" 'Of course, I cannot use H4 here.

I want a series of cells all compare the value in a relative cell
with that in an absolute cell. One example would be like this:

A5: =min(H4,E4)
A6: =min(H4,E5)
A7: =min(H4,E6)
A8: =min(H4,E7)

And I need to use a script (range.formula comes into my mind) to
fill in these formulae. Since I cannot use the script

range.formula = "=min(H4,R[-1]C[4])"

What do I do to replace H4 with something that works?

What is the address that gets the formula?

Since your R1C1 reference style formula uses relative rows/columns,
you'll have
to share the cell's address that would get the formula.

The problem is those formula were generated by a script using
range.formula command. What I need is a way to refer to a
particular cell while in R1C1 style and that is my original
question (post again in the following).

How do I refer to a particular cell when the spreadsheet is set to
r1c1 style? The spreadsheet does not accept H5 or things like
that. Thanks.

Dave Peterson wrote:

You can't mix R1C1 reference style with A1 reference style.

=min(h4,r[-1]c[4])
could be
=min(r4c8,r[-1]c[4])

Depending on what cell is getting the formula, you could use A1
reference style
in both spots.




John Smith wrote:


Dave Peterson wrote:


You should be able to refer to a range that way--no matter what the
display is
set for.

activesheet.range("H5").value = "hi there!"

works ok for me.

You may want to post what didn't work?


John Smith wrote:



How do I refer to a particular cell when the spreadsheet is set to
r1c1 style? The spreadsheet does not accept H5 or things like
that. Thanks.


I have the following formula in cells and they all show "#name?".

=min(h4,r[-1]c[4])

The way I got around it (in the old macro xlm file) was to set a
name, say TOMATOPRICE, to h4. Then I could use the formula:

=min(TOMATOPRICE,r[-1]c[4])

Don't know how to do that in vba.

 

[John Smith]

Got it to work. Thank you very much.

You could use R1C1 reference style:

With activesheet.range("A5:A8")
.formular1c1 = "=min(r4c8,r[-1]c[4])"
end with

or using A1 Reference style:

With activesheet.range("A5:A8")
.formula = "=min($h$4,E4)"
end with

Excel/vba will adjust the formula for each cell.

Just like when you select A5:A8 and type this formula:
=min($h$4,e4)
and hit ctrl-enter to fill all the selected cells.

Just make sure you write the formula for the top cell.

Sorry. I just realized that I did not explain my problem properly.
What I want to do is this:

range.formula = "=min(H4,R[-1]C[4])" 'Of course, I cannot use H4 here.

I want a series of cells all compare the value in a relative cell
with that in an absolute cell. One example would be like this:

A5: =min(H4,E4)
A6: =min(H4,E5)
A7: =min(H4,E6)
A8: =min(H4,E7)

And I need to use a script (range.formula comes into my mind) to
fill in these formulae. Since I cannot use the script

range.formula = "=min(H4,R[-1]C[4])"

What do I do to replace H4 with something that works?

What is the address that gets the formula?

Since your R1C1 reference style formula uses relative rows/columns, you'll have
to share the cell's address that would get the formula.



John Smith wrote:


The problem is those formula were generated by a script using
range.formula command. What I need is a way to refer to a
particular cell while in R1C1 style and that is my original
question (post again in the following).

How do I refer to a particular cell when the spreadsheet is set to
r1c1 style? The spreadsheet does not accept H5 or things like
that. Thanks.

Dave Peterson wrote:


You can't mix R1C1 reference style with A1 reference style.

=min(h4,r[-1]c[4])
could be
=min(r4c8,r[-1]c[4])

Depending on what cell is getting the formula, you could use A1 reference style
in both spots.




John Smith wrote:



Dave Peterson wrote:



You should be able to refer to a range that way--no matter what the display is
set for.

activesheet.range("H5").value = "hi there!"

works ok for me.

You may want to post what didn't work?


John Smith wrote:




How do I refer to a particular cell when the spreadsheet is set to
r1c1 style? The spreadsheet does not accept H5 or things like
that. Thanks.


I have the following formula in cells and they all show "#name?".

=min(h4,r[-1]c[4])

The way I got around it (in the old macro xlm file) was to set a
name, say TOMATOPRICE, to h4. Then I could use the formula:

=min(TOMATOPRICE,r[-1]c[4])

Don't know how to do that in vba.