Macro to autofill a IF function based on the column before it

I am working on a macro that designed to reformat data and it is coming along pretty well, but I keep getting the following error

Run-time error '1004':
Application-defined or object-defined error

Here is the code

Sub BeginReformat()
'Piecing together a macro
    ActiveWorkbook.Sheets(1).Sort.SortFields.Clear
    ActiveWorkbook.Sheets(1).Sort.SortFields.Add Key:=Range("R:R" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Sheets(1).Sort
        .SetRange Range("A:V")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        Range("C:C,E:E,F:F").Select
        Range("F1").Activate
        Selection.Insert Shift:=xlToRight
        Range("C2").Select
        Range("C2").FormulaR1C1 = "=(RC[20]&""-""&RC[-2]&""-""&RC[1])"
        LastRow = ActiveSheet.UsedRange.Rows.Count
        Range("C2").AutoFill Destination:=Range("C2:C" & LastRow)
        Range("F2").Select
        Range("F2").FormulaR1C1 = "=IF(Sheet1!$A$1 RC[-1]>=1,""Yes"",""No"")"
        LastRow = ActiveSheet.UsedRange.Rows.Count
        Range("F2").AutoFill Destination:=Range("F2:F" & LastRow)
    End With
End Sub

The offending line is

Range("F2").FormulaR1C1 = "=IF(Sheet1!$A$1 RC[-1]>=1,""Yes"",""No"")"

I am sure that I calling this wrong but I am unsure as to how I should be calling it

Never "autofill" (a two step process) when you can insert the formulas into the entire range all at once in the first step:

Range("C2:C" & LR).FormulaR1C1 = "=RC23 & ""-"" & RC1 & ""-"" & RC4"

It looks like your formula insertion code is inside the WITH .SORT stuff, it shouldn't be there.

Option Explicit

Sub BeginReformat()
Dim LastRow As Long

'Piecing together a macro
    ActiveWorkbook.Sheets(1).Sort.SortFields.Clear
    ActiveWorkbook.Sheets(1).Sort.SortFields.Add Key:=Range("R:R"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    
    With ActiveWorkbook.Sheets(1).Sort
        .SetRange Range("A:V")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
        
    Range("C:C,E:E,F:F").Insert Shift:=xlToRight
    
    LastRow = Cells.Find("*", Cells(Rows.Count, Columns.Count), _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        
    Range("C2:C" & LastRow).FormulaR1C1 = "=RC23 & ""-"" & RC1 & ""-"" & RC4"
    Range("F2:F" & LastRow).FormulaR1C1 = "=IF(Sheet1!$A$1 RC5>=1,""Yes"",""No"")"

End Sub

I took out the UsedRange method for lastrow since there are some hidden traps with that which could result in a bad answer filling that variable.

Thank you JBeaucaire for taking the time to examine my macro, but I am still getting the same error even with your revised code:

the line that is problematic:

Range("F2:F" & LastRow).FormulaR1C1 = "=IF(Sheet1!$A$1 RC5>=1,""Yes"",""No"")"

I thought that I had created a dynamic sheet so that any workbook that I open that need this particular type of formatting I could format without explicitly call/reference the name of the workbook, but instead would act on the first sheet in the workbook that needs this formating.


I had asked in this thread how to even set up the formula and thought that I had refenced the sheet as a dynamic sheet, but something is still wrong


I am using Excel 2007 on Windows XP

So your saying you're trying this code on a workbook with no sheet called Sheet1? That would cause this.

You'll need to construct that formula with a sheet name variable in place of where Sheet1 is currently.

Option Explicit

Sub BeginReformat()
Dim LastRow     As Long
Dim FirstSht    As String

FirstSht = Sheets(1).Name

'Piecing together a macro
    ActiveWorkbook.Sheets(1).Sort.SortFields.Clear
    ActiveWorkbook.Sheets(1).Sort.SortFields.Add Key:=Range("R:R"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    
    With ActiveWorkbook.Sheets(1).Sort
        .SetRange Range("A:V")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
        
    Range("C:C,E:E,F:F").Insert Shift:=xlToRight
    
    LastRow = Cells.Find("*", Cells(Rows.Count, Columns.Count), _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        
    Range("C2:C" & LastRow).FormulaR1C1 = "=RC23 & ""-"" & RC1 & ""-"" & RC4"
    Range("F2:F" & LastRow).FormulaR1C1 = "=IF('" & FirstSht & "'!$A$1 RC5>=1,""Yes"",""No"")"

End Sub

Originally Posted by capnhud

the sheet is the 1st sheet and not sheet one. I thought that I was using an index instead of a name. How am I supposed to reference the first sheet?

I've shown you how to do that in post #5. Look for all references/use of FirstSht variable.

Try it and see. Use F8 to step through the code one line at a time. After that line of code executes, hover your mouse over the word FirstSht and you'll see what the current value is.

When i try to run the macro from what you have posted I am receiving the same error

Run-time error '1004':
Application-defined or object-defined error

and it corresponds to this line of code when I indicate to debug

 Range("F2:F" & LastRow).FormulaR1C1 = "=IF('" & FirstSht & "'!$A$1 RC5>=1,""Yes"",""No"")"

edit: FirstSht when I mouse over it says FirstSht = "" which to me means that nothing is being specified yet is that correct?

Is this any better?

Range("F2:F" & LastRow).FormulaR1C1 = "=IF('" & FirstSht & "'!RC5>=1,""Yes"",""No"")"

Check that the formula inserted in column F is correct.