How to change Formula R1C1 in to normal when recording macro in VBA

[narendra_1]

Respected Seniors,

When I am recording a macro in Excel VBA it showing like below given formula

ActiveCell.FormulaR1C1 = "=TRIM(R[-9]C[-2])"

I want to change it like :-

ActiveCell.range("A1").value style without R [-9] C[-2]

is it possible

Regards

Narendra

www.Excelnsg.com

 

[Fluff]

The macro recorder will always record formulas as R1C1 & there is no way of changing that.
If you want A1 style formulae, you'll either have to write the macro, or modify a recorded macro.

 

[Rick Rothstein]

The macro recorder will always record formulas as R1C1 & there is no way of changing that.
If you want A1 style formulae, you'll either have to write the macro, or modify a recorded macro.

To follow up...
You can find the A1 notation for any formula given in R1C1 notation, or vice versa, by changing the display mode for Excel. Assuming you are in A1 display mode and you want to find the A1 formula for this R1C1 formula...

=TRIM(R[-9]C[-2])

Since it is an R1C1 formula, switch Excel's display format to R1C1 display mode. You do this through Excel Options. How to open the Excel Options dialog box differs from version to version, so you will need to find out how to do this from your version of Excel's help files. Okay, now that you have Excel Options open, select the Formula item on the left and look for a section titled similar to "Working with formulas" in which you will find a checkbox with the caption "R1C1 reference style"... put a checkmark in it and then click the OK button. Excel is now in R1C1 display mode (you can tell because the column header letters have now turned to numbers). Copy paste the above formula into whatever cell it was originally destined for (to do this, you will need to convert the column letter for the cell to a column number, the row is already a number). For the above formula, let's say that cell (the ActiveCell in your example code line) is E10... this cell is located at the intersection of Row 10, Column 5. Now open Excel Options again and put it back into A1 display mode by unchecking the "R1C1 reference style" checkbox (followed by clicking the OK button). Now select cell E10 (the cell you copied the formula into) and look in the Formula Bar, you will see the formula in A1 notation.

 

[John_w]

When I am recording a macro in Excel VBA it showing like below given formula

ActiveCell.FormulaR1C1 = "=TRIM(R[-9]C[-2])"

I want to change it like :-

ActiveCell.range("A1").value style without R [-9] C[-2]

You can use Application.ConvertFormula, like this:

ActiveCell.Formula = Application.ConvertFormula("=TRIM(R[-9]C[-2])", xlR1C1, xlA1, False, ActiveCell)

 

[Legacy 456155]

Thanks John! Great tip and exactly what I was looking for. I've not even heard of this function.

 

[RoryA]

Just note that there is a 255 character limitation with that.

 

[Legacy 456155]

Ok. Thanks Rory.