Converting basic formula to R1C1 notation

[windb]

Hi there,

Please can someone help convert =IF(F2=F1,"Y", "N") into R1C1 notation?

Context
I am inputting a formula in column AU which identifies duplicate values based on values in column F.

In excel my formula in cell AU2 would be =IF(F2=F1,"Y", "N").

Cell AU3 would be then be =IF(F3=F2,"Y", "N") etc etc

However, I'm struggling to convert this to R1C1 notation , as I'm fairly new to VBA

My VBA formula is as follows:

' DuplicatesTest Macro
'
Range("AU2").Select
'ActiveCell.FormulaR1C1 = "=IF(R2C6=R[-1]C,""Y"", ""N"")"
Range("AU2").Select
Selection.AutoFill Destination:=Range("AU2:AU5")
Range("AU2:AU5").Select

One way or another, I can't return the right value to AU2 and/ or I can't ensure the column stays fixed (absolute), and the rows remain variable in the formula as the formula fills down. R[-1] in the formula above looks up the value one row above cell AU2, so I know this is incorrect.

I've been struggling on this for about an hour so any help would be appreciated. (Additionally, it's worth noting that I will be writing some other formulas like this so I'm not sure if there is a general steer/ approach to be following here). I think I may be able to put in a relative cell value (e.g. RC-32 to locate the cell from AU2 but will I always need to do that? i.e. can I not simply refer directly to the F2 cell, for example, in R1C1 notation?)

Many thanks

 

[Joe4]

The easiest thing to do is to let Excel do it for you.
Simply turn on the Macro Recorder, and manually enter the formula, exactly as it should appear, in a cell on your worksheet.
Then stop the Macro Recorder and view the code you recorded. It will show you the formula in the R1C1 format that you need.
So you can copy/paste it into your VBA code.

 

[Jon Peltier]

I typed your original formula into cell AU2:
=IF(F2=F1,"Y", "N")

Then I went to the VB Editor, and typed this into the Immediate Window*:
? activecell.FormulaR1C1

This was returned to the Immediate Window:
=IF(RC[-41]=R[-1]C[-41],"Y","N")

These are relative addresses, just like the F1 and F2 in your A1-style formula.

You seemed troubled about the reference to column F. If you want to make that reference absolute, you need a dollar signed before the column letter:
=IF($F2=$F1,"Y", "N")

Now my conversation with the Immediate Window looks like this:
? activecell.FormulaR1C1
=IF(RC6=R[-1]C6,"Y","N")

The row references are relative, the column references are absolute.

*I could have just changed the reference style to R1C1 to see how the formula changed.

 

[windb]

I typed your original formula into cell AU2:
=IF(F2=F1,"Y", "N")

Then I went to the VB Editor, and typed this into the Immediate Window*:
? activecell.FormulaR1C1

This was returned to the Immediate Window:
=IF(RC[-41]=R[-1]C[-41],"Y","N")

These are relative addresses, just like the F1 and F2 in your A1-style formula.

You seemed troubled about the reference to column F. If you want to make that reference absolute, you need a dollar signed before the column letter:
=IF($F2=$F1,"Y", "N")

Now my conversation with the Immediate Window looks like this:
? activecell.FormulaR1C1
=IF(RC6=R[-1]C6,"Y","N")

The row references are relative, the column references are absolute.

*I could have just changed the reference style to R1C1 to see how the formula changed.

Many thanks Jon. This would seem to be along the right lines.

I'm receiving a compile error, however, with the below code which states, 'invalid use of property'. (The Formula R1C1 part highlights in blue after clicking "ok"). I've tried with and without the speech marks and having only single speech marks around the text. Any thoughts on this?

Range("AU2").Select
ActiveCell.FormulaR1C1 "=IF(RC6=R[-1]C6,""Y"",""N"")"
'ActiveCell.FormulaR1C1 "=IF(RC[-41]=R[-1]C[-41],""Y"",""N"")"
Range("AU2").Select
Selection.AutoFill Destination:=Range("AU2:AU5")
Range("AU2:AU3").Select

 

[windb]

The easiest thing to do is to let Excel do it for you.
Simply turn on the Macro Recorder, and manually enter the formula, exactly as it should appear, in a cell on your worksheet.
Then stop the Macro Recorder and view the code you recorded. It will show you the formula in the R1C1 format that you need.
So you can copy/paste it into your VBA code.

Many thanks Joe, yes have tried that a few times and generally it has worked really well with the several other formulas I have managed to record. Stumped on this one.

 

[Joe4]

Many thanks Joe, yes have tried that a few times and generally it has worked really well with the several other formulas I have managed to record. Stumped on this one.

I fail to see the issue.

If you do what I told you, you get something like this:

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("AU2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-41]=R[-1]C[-41],""Y"", ""N"")"
    Range("AU3").Select
End Sub

So the part in red is what you need.

If you want to apply this formula to the range AU2:AU5, there is no need to use autofill. You can apply the formula to the whole range at once like this:

    Range("AU2:AU5").FormulaR1C1 = "=IF(RC[-41]=R[-1]C[-41],""Y"", ""N"")"

 

[windb]

I fail to see the issue.

If you do what I told you, you get something like this:

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("AU2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-41]=R[-1]C[-41],""Y"", ""N"")"
    Range("AU3").Select
End Sub

So the part in red is what you need.

If you want to apply this formula to the range AU2:AU5, there is no need to use autofill. You can apply the formula to the whole range at once like this:

    Range("AU2:AU5").FormulaR1C1 = "=IF(RC[-41]=R[-1]C[-41],""Y"", ""N"")"

Thank you, that’s worked. I think I must have been conflating two different tasks. Thanks for the additional clarification.

 

[Joe4]

You are welcome.

Yes, things can get messy with formula when dealing with R1C1 format and with double-quotes in your formulas. Rather than trying to figure it out on my own, I always use the Macro Recorder to make Excel do all the hard work!

 

[Jon Peltier]

Many thanks Jon. This would seem to be along the right lines.

I'm receiving a compile error, however, with the below code which states, 'invalid use of property'. (The Formula R1C1 part highlights in blue after clicking "ok"). I've tried with and without the speech marks and having only single speech marks around the text. Any thoughts on this?

Range("AU2").Select
ActiveCell.FormulaR1C1 "=IF(RC6=R[-1]C6,""Y"",""N"")"
'ActiveCell.FormulaR1C1 "=IF(RC[-41]=R[-1]C[-41],""Y"",""N"")"
Range("AU2").Select
Selection.AutoFill Destination:=Range("AU2:AU5")
Range("AU2:AU3").Select

You left out an equals sign.

    ActiveCell.FormulaR1C1 = "=IF(RC6=R[-1]C6,""Y"",""N"")"

 

[Jon Peltier]

Why do you have to use FormulaR1C1? You can just use Formula. Excel will sort it out.