FormulaR1C1 Not Working?

jalrs

Active Member
Joined
Apr 6, 2022
Messages
300
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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:

VBA 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!
 
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.
 
Upvote 0
Solution

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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:

VBA Code:
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
 
Upvote 0
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:

VBA 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
 
Upvote 0
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 (y)
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!
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top