Typing out Excel formula in Excel Macro

[gomes123]

I have this line of code that I require an excel macro to type out, and drag and drop down.

=IF(AND(E3>C3,G3>E3,F3>D3,H3>F3),"UP",IF(AND(C3>E3,E3>G3,D3>F3,F3>H3),"DOWN",IF(AND(E3>C3,G3>E3,D3>F3,F3>H3),"LEFT",IF(AND(C3>E3,E3>G3,F3>D3,H3>F3),"RIGHT","NO"))))

Recording an excel macro it turned out to be:

ws.Range("I3").FormulaR1C1 = "=IF(AND(RC[-4]>RC[-6],RC[-2]>RC[-4],RC[-3]>RC[-5],RC[-1]>RC[-3]),""UP"",IF(AND(RC[-6]>RC[-4],RC[-4]>RC[-2],RC[-5]>RC[-3],RC[-3]>RC[-1]),""DOWN"",IF(AND(RC[-4]>RC[-6],RC[-2]>RC[-4],RC[-5]>RC[-3],RC[-3]>RC[-1]),""LEFT"",IF(AND(RC[-6]>RC[-4],RC[-4]>RC[-2],RC[-3]>RC[-5],RC[-1]>RC[-3]),""RIGHT"",""NO""))))"

I'm just wondering why the excel macro types it out as "RC" instead of the cells such as E3, C3 (feels more difficult to read, when reading through the excel macro code). Is this a "best practices" way of writing the formula in the excel macro, is the code above fine, or is there a better (more efficient) way to write it out? Thanks!

Complete code is below:

Sub Breakthrough()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Main")
  
    Dim LastRow As Long
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
  

    ws.Range("I3").FormulaR1C1 = "=IF(AND(RC[-4]>RC[-6],RC[-2]>RC[-4],RC[-3]>RC[-5],RC[-1]>RC[-3]),""UP"",IF(AND(RC[-6]>RC[-4],RC[-4]>RC[-2],RC[-5]>RC[-3],RC[-3]>RC[-1]),""DOWN"",IF(AND(RC[-4]>RC[-6],RC[-2]>RC[-4],RC[-5]>RC[-3],RC[-3]>RC[-1]),""LEFT"",IF(AND(RC[-6]>RC[-4],RC[-4]>RC[-2],RC[-3]>RC[-5],RC[-1]>RC[-3]),""RIGHT"",""NO""))))"

    Range("C3:L" & Range("A" & Rows.Count).End(xlUp).Row).FillDown
  
    Range("I2").Select
    Dim LastRow2 As Long
LastRow2 = Range("A" & Rows.Count).End(xlUp).Row
Selection.AutoFilter
ActiveSheet.Range("$A$2:$L$" & LastRow2).AutoFilter Field:=9, Criteria1:="UP"
ActiveWorkbook.Worksheets("Main").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Main").AutoFilter.Sort.SortFields.Add Key:=Range( _
"J2:J" & LastRow2), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Main").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

  
End Sub

 

[Macropod]

Your code is using the R1C1 format because that's what the recorder told it to do. You could have used:

ws.Range("I3").Formula = "=IF(AND(E3>C3,G3>E3,F3>D3,H3>F3),""UP"",IF(AND(C3>E3,E3>G3,D3>F3,F3>H3),""DOWN"",IF(AND(E3>C3,G3>E3,D3>F3,F3>H3),""LEFT"",IF(AND(C3>E3,E3>G3,F3>D3,H3>F3),""RIGHT"",""NO""))))"

The recorder is about as smart as a box of rocks.