How to convert VBA IF formula into R1C1 format

[MixedUpExcel]

Hi,

Quick question as I just can't figure it out, please can someone show me how to convert the following into R1C1 format:

MyColumn in this case is where I have a cell selected - in this case I have selected a cell somewhere in Column A but in the future, I might select Column F or Z etc etc.

With Range(Cells(2, MyColumn + 1), Cells(lr, MyColumn + 1))
  .Formula = [B][COLOR=#b22222]"=IF(A2<>"""",""a"","""")"[/COLOR][/B]
  .Value = .Value
End With

Thanks.

 

[RoryA]

Assuming you want to refer to the column to the left of the formula column:

.FormulaR1C1 = "=IF(RC[-1]<>"""",""a"","""")"

will do the job.

 

[MixedUpExcel]

Perfect.

Thanks Rory for the quick response.

Simon

 

[RoryA]

Glad to help.

 

[Joe4]

A little trick you can use, especially if you want to see what a formula looks like in R1C1 format.
That is the format that the Macro Recorder uses, by default.
So, if you turn on the Macro Recorder, and record yourself manually entering the formula directly into your sheet, and stop the recorder, you can view the code and see what the formula needs to look like!

 

[RoryA]

I use the immediate window in the VB Editor and enter:

?Replace$(activecell.formular1c1, """", """""")

which outputs the correct R1C1 style formula with any quotes doubled up so it can be used directly in my code.