Help with FormulaR1C1 with IF using absolute comparison columns and rows

[New2Dev]

This is my first time posting in any forum but this problem has me stumped. Any help would be appreciated.

I have a worksheet with dates along the top row and dates along the right column, I need to place a "1" in the intersecting cells where the dates match. I am attempting to use the below code, but cannot get the absolute values for the top row in the formula of each cell.

(Dates are in Column A and need to be compared across dates in row 1)

Private Sub CommandButton1_Click()
Dim c As Range, r As Range
Dim num As String
num = "1"
Set c = Sheets("Sheet7").Range("B2:G12")
For Each r In c
r.FormulaR1C1 = "=IF(RC1=R[-1]C[0], " & """1""" & ", " & """""" & " )"
Next r
End Sub

 

[Scott Huish]

Hi, welcome to the board!

That's a lot of code for that.

Does this do what you want?

Private Sub CommandButton1_Click()
Sheets("Sheet7").Range("B2:G12").Formula = "=IF($A2=B$1,1,"""")"
End Sub

 

[New2Dev]

Thanks Hotpepper!!! I won't say how long I spent working on that.

Much appreciated.