Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming,microsoft.public.excel.functions
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.programming,microsoft.public.excel.functions
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming,microsoft.public.excel.functions
|
|||
|
|||
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. |
#12
Posted to microsoft.public.excel.programming,microsoft.public.excel.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum Indirect Using R1C1 Style | Excel Worksheet Functions | |||
How do I change sheet notation from R1C1 style to A1 style in XL 2 | Setting up and Configuration of Excel | |||
How to use/evaluate another Cell Formula in R1C1 style | Excel Programming | |||
can a1 reference style and r1c1 style be used in same formula? | Excel Worksheet Functions | |||
R1C1 reference style | Excel Discussion (Misc queries) |