FormulaR1C1 Not Working?

[jalrs]

Hello guys,

I'm trying to add a formulaR1C1 to my code but it's not working.
I'm doing this because I'm clearing used range contents firstly, to avoid double clicking, but it removes the excel formula. Therefore, I need to add the formula through the code by R1C1 way.

Happens it is not working. I did it by triggering macro recorder -> f2 -> enter -> disabling macro recorder as Joe4 taught me once.

both codes that the macro recorder gave me were:

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(RC[-4]="""","""",VLOOKUP(RC4&RC5,'Matriz Áreas'!C1:C5,4,0)),""VALIDAR"")"
    Range("H3").Select
End Sub
Sub Macro3()
'
' Macro3 Macro
'

'
    Range("I2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(RC[-1]="""","""",VLOOKUP(R[3]C4&R[3]C5,'Matriz Áreas'!C1:C5,5,0)),""VALIDAR"")"
    Range("I3").Select
End Sub

I then adapted it (accordingly to the formulaR1C1 I have working on another module) as I want it to perform on whole column, starting from row 2, and not on a certain range:
Here is my code:

Sub integrar()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim wb As Workbook, ws As Worksheet
dim lr1 as long, lr2 as long

Set wb = ThisWorkbook
Set ws = wb.Worksheets("TFDB")

ws.UsedRange.Offset(1).ClearContents

Call integrarf1
Call integrarf2

lr1 = ws.cells(rows.count, "D").End(xlup).Row
lr2 = ws.cells(rows.count, "H").End(xlup).Row

if lr1 > 1
      
            Range("H2:H" & lr1).FormulaR1C1 = _
            "=IFERROR(IF(RC[-1]="""","""",VLOOKUP(RC[-1],Matriz Áreas!C[-1]:C[-5],4,0)),""VALIDAR"")"
          
        End If

if lr2 > 1

            Range("H2:H" & lr2).FormulaR1C1 = _
            "=IFERROR(IF(RC[-4]="""","""",VLOOKUP(RC[-4],Matriz Áreas!C[-1]:C[-5],5,0)),""VALIDAR"")"

end if

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Any help is greatly appreciated

Thanks!

 

[FormR]

The formulas are not appearing on the sheet.

If the sheet you want the formulas to appear on is the one called "TFDB" and that is not the active sheet when you run the code then you need to qualify the range like this:

ws.Range("H2:H" & lr1).FormulaR1C1 = _
"=IFERROR(IF(RC[-1]="""","""",VLOOKUP(RC[-1],'Matriz Áreas'!C[-1]:C[-5],4,0)),""VALIDAR"")"

And the same for the second formula.

 

[jalrs]

If the sheet you want the formulas to appear on is the one called "TFDB" and that is not the active sheet when you run the code then you need to qualify the range like this:

ws.Range("H2:H" & lr1).FormulaR1C1 = _
"=IFERROR(IF(RC[-1]="""","""",VLOOKUP(RC[-1],'Matriz Áreas'!C[-1]:C[-5],4,0)),""VALIDAR"")"

And the same for the second formula.

Well, It worked for the first column but not for the second.

Column H gets autopopulated as it should, column I should be autopopulated after column H, that's why there's is the lr2, but didn't happen.

Code is now like:

Sub integrarrrr()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim wb As Workbook, ws As Worksheet
Dim lr1 As Long, lr2 As Long

Set wb = ThisWorkbook
Set ws = wb.Worksheets("TFDB")

ws.UsedRange.Offset(1).ClearContents

Call integrarf1
Call integrarf2

lr1 = ws.Cells(Rows.Count, "D").End(xlUp).Row
lr2 = ws.Cells(Rows.Count, "H").End(xlUp).Row

    If lr1 > 1 Then
 
        ws.Range("H2:H" & lr1).FormulaR1C1 = _
        "=IFERROR(IF(RC[-4]="""","""",VLOOKUP(RC4&RC5,'Matriz Áreas'!C1:C5,4,0)),""VALIDAR"")"
     
    End If
 
    If lr2 > 1 Then
 
        ws.Range("I2:I" & lr2).FormulaR1C1 = _
        "=IFERROR(IF(RC[-1]="""","""",VLOOKUP(RC4&RC5,'Matriz Áreas'!C1:C5,5,0)),""VALIDAR"")"
     
    End If
 
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

 

[jalrs]

Hello once again @FormR

I don't know why it wasn't working, let's say I was just overcomplicating it.
Marked your answer as solution, since it triggered the appearance of the formula

Working code:

Sub integrar()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim wb As Workbook, ws As Worksheet
Dim lr1 As Long, lr2 As Long

Set wb = ThisWorkbook
Set ws = wb.Worksheets("TFDB")

ws.UsedRange.Offset(1).ClearContents

Call integrarf1
Call integrarf2

lr1 = ws.Cells(Rows.Count, "C").End(xlUp).Row

    If lr1 > 1 Then
   
        ws.Range("G2:G" & lr1).FormulaR1C1 = _
        "=TODAY()"
 
        ws.Range("H2:H" & lr1).FormulaR1C1 = _
        "=IFERROR(IF(RC[-4]="""","""",VLOOKUP(RC4&RC5,'Matriz Áreas'!C1:C5,4,0)),""VALIDAR"")"
       
        ws.Range("I2:I" & lr1).FormulaR1C1 = _
        "=IFERROR(IF(RC[-1]="""","""",VLOOKUP(RC4&RC5,'Matriz Áreas'!C1:C5,5,0)),""VALIDAR"")"
    
    End If
 
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Thank you very much FormR

 

[FormR]

I don't know why it wasn't working

It looks like you were setting lr2 based on the last used row in column H, but doing so before you populate column H with the formulas.

Working code:

Good to hear

 

[jalrs]

It looks like you were setting lr2 based on the last used row in column H, but doing so before you populate column H with the formulas.



Good to hear

I edited my previous reply so not sure if you saw my thanks message. Just in case here it is.

Thanks for your time FormR!