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