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]

Hi, in the code the recorder generated there are single quotes around the sheet name which you appear to have missed when adapting it.

'Matriz Áreas'

 

[jalrs]

Hi, in the code the recorder generated there are single quotes around the sheet name which you appear to have missed when adapting it.

Hello FormR and thanks for your input.

I will try that, even tho I left them out on purpose according to the other formular1c1 I have working with the help of Joe4.

I will comeback if needed.

Thanks

 

[jalrs]

Hi, in the code the recorder generated there are single quotes around the sheet name which you appear to have missed when adapting it.

Hello again FormR,

Added those and still not working.

Thanks

 

[FormR]

Hi, can you please post the code as you now have it and elaborate a little on the "not working" part?

even tho I left them out on purpose according to the other formular1c1 I have working

If you have spaces (or a few other special characters) then they are needed.

 

[jalrs]

Hi, can you please post the code as you now have it and elaborate a little on the "not working" part?



If you have spaces (or a few other special characters) then they are needed.

Hello, thanks for keeping with me.

Sure I can:

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 Then
 
        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 Then
 
        Range("I2:I" & 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

Additional notes I believe interest you:
Formulas I converted from excel to r1c1:

=IFERROR(IF(D2="";"";VLOOKUP($D2&$E2;'Matriz Áreas'!$A:$E;4;0));"VALIDAR")

^column H

=IFERROR(IF(H2="";"";VLOOKUP($D5&$E5;'Matriz Áreas'!$A:$E;5;0));"VALIDAR")

^column I

Sheet "Matriz Áreas" as well as the current and the expected output follow as attachments:

Thanks FormR

EDIT: I know current/expected output are in different sheets. I just did that to show an example of what I pretend. Final Output should be displayed on TFDB. Tipificação Feedback was just to give the example.

 

[FormR]

Hi, the code you in post #6 runs without error for me, if you are getting a runtime error, what is the exact error message and on what line does it occur?

These are not the formulas that the code in post #5 produces?

Formulas I converted from excel to r1c1:

=IFERROR(IF(D2="";"";VLOOKUP($D2&$E2;'Matriz Áreas'!$A:$E;4;0));"VALIDAR")

^column H

=IFERROR(IF(H2="";"";VLOOKUP($D5&$E5;'Matriz Áreas'!$A:$E;5;0));"VALIDAR")

^column I

 

[jalrs]

Hi, the code you in post #6 runs without error for me, if you are getting a runtime error, what is the exact error message and on what line does it occur?

These are not the formulas that the code in post #5 produces?

Hi FormR,

The code works, problem is regarding R1C1 formula, that's what not working and I don't know why. I did what I was taught to do on another thread. Enable Macro Recorder -> F2 on the cell I wanna see the formula -> Enter -> Stop Macro Recorder -> Et voilá.

As you can see on the attachments, expected output =/= current output. Columns H and I should be auto populated according to Matriz Areas, that's why I'm adding those formulas back in the end of the code, since at the beginnig I clear contents to avoid double clicking.

Hope you can help me,

Thanks FormR

 

[FormR]

problem is regarding R1C1 formula, that's what not working

Hi, what does not working mean? Note - the pictures you attached do little to help explain.

For example :
- are the formulas not appearing on the sheet you expect?
- or are they not formulas you expect?
- or do they not do what you expect?
- or something else

Please try to be specific.

 

[jalrs]

Hi, what does not working mean? Note - the pictures you attached do little to help explain.

For example :
- are the formulas not appearing on the sheet you expect?
- or are they not formulas you expect?
- or do they not do what you expect?
- or something else

Please try to be specific.

Hello again FormR,

Sorry, I thought It was clear already. The formulas are not appearing on the sheet. The R1C1 formulas I add should produce the expected output on columns H and I as you can see on expected output picture.

What you can see on the expected output picture is what I manually did: Run the macro and then pasted the excel formula to both columns, so I did it manually.

I can't keep with the excel formula because I'm clearing cell contents on the beginning of the code, to avoid double clicking, therefore I converted both formulas to R1C1 style in order to be able to add them through VBA, in the end of the code, and still get the desired output.

Hope it is now clear, if not, tell me.

Thanks