Re: What is the FormulaR1C1 method?

  • Thread starter Thread starter Bob Umlas
  • Start date Start date
B

Bob Umlas

Excel 97 DOES recognize it. When you're not putting in a formula, tho,
ActiveCell.Value works fine.
By the way, no need to select A1:
Range("A1").Value = ...
If A1 were to contain =B2. then you could use
Range("A1").Formula = "=B2"
or
Range("A1").FormulaR1C1 = "=r[1]c[1]"
which means one row down & 1 column to the right.

A live, online Excel Master Class is starting in September (which I'm
teaching).
For details, please follow this link:

http://www.iil.com/str_link_all_results.asp?select_cartid=395
Bob Umlas
Excel MVP
 
It has nothing to do with calling a function. It has to do with how you've written the formula.

If the arguments do NOT include cell references -- only literal values or range names -- it
doesn't matter which one you use.

OTOH, if you do have cell references, Formula requires that you type them in A1 format;
FormulaR1C1 requires that you use the R1C1 reference style. An absolute reference to B22 is
$B$22 in A1 style, R22C2 in R1C1 style.

If you use the Formula property but write a reference in R1C1 style, you'll get an error since
R22C22 isn't a valid A1 reference, and it can't be a named range because it "looks like" an R1C1
reference.

FormulaR1C1 IS in VBA Help. Type FormulaR1C1 in the immediate window and then press F1 if you
can't find it otherwise.


Thanks. I am still a little confused though. The (existing) code that I am
looking at does this.

Range("A1").Select
ActiveCell.FormulaR1C1 = "=wxyz(""param1"", ""param2"")"

Are you saying that I instead could do:
Range("A1").Formula = "=wxyz(""param1"", ""param2"")"

Are you also saying that "Formula" must be used because I am calling a
function?

Thanks!


Bob Umlas said:
Excel 97 DOES recognize it. When you're not putting in a formula, tho,
ActiveCell.Value works fine.
By the way, no need to select A1:
Range("A1").Value = ...
If A1 were to contain =B2. then you could use
Range("A1").Formula = "=B2"
or
Range("A1").FormulaR1C1 = "=r[1]c[1]"
which means one row down & 1 column to the right.

A live, online Excel Master Class is starting in September (which I'm
teaching).
For details, please follow this link:

http://www.iil.com/str_link_all_results.asp?select_cartid=395
Bob Umlas
Excel MVP

hmmm... said:
The following is some code that I am looking at:

Range("A1").Select
ActiveCell.FormulaR1C1 = "=<interface to 3rd party DDE
server>"

What is the FormulaR1C1 method? Excel 97 does not
recognize it. Is it Excel 2000?

Would

Range("A1").Select
ActiveCell.value = "=<interface to 3rd party DDE server>"

do the same thing? Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Did you look in the help for VBA?

But I could find info in excel's help when I searched for "range reference".

I looked under:
"about cell and range references"


Hmmm... said:
Also, why is there no mention of FormulaR1C1 in the Excel help? Or am I
just failing to find it? Thanks.

Bob Umlas said:
Excel 97 DOES recognize it. When you're not putting in a formula, tho,
ActiveCell.Value works fine.
By the way, no need to select A1:
Range("A1").Value = ...
If A1 were to contain =B2. then you could use
Range("A1").Formula = "=B2"
or
Range("A1").FormulaR1C1 = "=r[1]c[1]"
which means one row down & 1 column to the right.

A live, online Excel Master Class is starting in September (which I'm
teaching).
For details, please follow this link:

http://www.iil.com/str_link_all_results.asp?select_cartid=395
Bob Umlas
Excel MVP

hmmm... said:
The following is some code that I am looking at:

Range("A1").Select
ActiveCell.FormulaR1C1 = "=<interface to 3rd party DDE
server>"

What is the FormulaR1C1 method? Excel 97 does not
recognize it. Is it Excel 2000?

Would

Range("A1").Select
ActiveCell.value = "=<interface to 3rd party DDE server>"

do the same thing? Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your reply. Unfortunately I can't find anything in VBA Help
(Excel 97). Is this documented in VBA Excel 2000?
 
I don't have XL97 on my hard drive, but I can't really believe it isn't in Help. Did you do what
I suggested re F1? If that doesn't work, press F2 to get into the object browser, select the
Range object in the lower section, then its FormulaR1C1 property, and click on the yellow
question mark.
 
I just dug out my XL97 VBA Help files. It IS there. Are you sure you have *VBA* help installed
(I doubt you'll find it in regular Excel Help.)
 
Back
Top