refer to a particular cell in r1c1 style
 

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.

refer to a particular cell in r1c1 style
 

You can learn quickly by clicking cells instead of typing their addresses.
Or temporarily change to A1 style (ToolsOptionsGeneral 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


"John Smith" wrote in message ...
| 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.

refer to a particular cell in r1c1 style
 

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.

--

Dave Peterson

refer to a particular cell in r1c1 style
 

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.

refer to a particular cell in r1c1 style
 

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

refer to a particular cell in r1c1 style
 

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 (ToolsOptionsGeneral 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

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



"John Smith" wrote in message ...
| 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.

refer to a particular cell in r1c1 style
 

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.

refer to a particular cell in r1c1 style
 

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.



--

Dave Peterson

refer to a particular cell in r1c1 style
 

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?

Dave Peterson wrote:
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.

refer to a particular cell in r1c1 style
 

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.

John Smith wrote:

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?

Dave Peterson wrote:
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.




--

Dave Peterson

refer to a particular cell in r1c1 style
 

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

--
Regards,
Tom Ogilvy


"John Smith" wrote in message
...
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?

Dave Peterson wrote:
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.

refer to a particular cell in r1c1 style
 

Got it to work. Thank you very much.

Dave Peterson wrote:
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.

John Smith wrote:

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?

Dave Peterson wrote:

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.