No AutoFill because of data constraint

[BillTony]

Hi there!

I'm getting a runtime 1004: AutoFill method of Range class failed.

I'm sequentially adding to a data set, so the last row containing data will change from day to day.

However, the size increase is variable so, unpredictable.

If column B is longer (more rows) than column C, the routine runs as it should.

If both columns are the same length - basically meaning that a single (1) record is added on a given day - the process fails (essentially, there is nothing to AutoFill).

The routine fails on the "Selection.AutoFill Destination..." line.

I think the commented code below should make it reasonably clear what I'm attempting...

Thanks in advance!

'Find the 1ST BLANK CELL in column A.
'BUT - YOU WILL BE OFFSETTING TO SELECT THE EQUIVALENT CELL IN COLUMN C.
    Range("A1").End(xlDown).Offset(1, 2).Select
    
'Enter an IF Statement in cell C2.
'The PREMISE: If there is a #N/A value in column D.
'The value in column C will be BLANK.
'ELSE, the value in column C will be "EXC."
    ActiveCell.FormulaR1C1 = "=IF(ISNA(RC[1]),"""",""EXC"")"
    
'If the LAST ROW containing data in column B is LARGER (higher row number),
'than the row number you are currently in in column C (via the Offset statement),
'AutoFill column C by the last row in column B.
'Otherwise - MOVE ON...
    If Last_Row_ColB > Last_Row_ColC Then
        Selection.AutoFill Destination:=Range(ActiveCell.Address, Cells(Last_Row_ColB, ActiveCell.Column))
    End If

 

[Fluff]

Try

    Dim Rng As Range

    Set Rng = Range("A1").End(xlDown).Offset(1, 2)
    With Range(Rng, Range("B" & Rows.Count).End(xlUp).Offset(, 1))
        .FormulaR1C1 = "=IF(ISNA(RC[1]),"""",""EXC"")"
    End With

 

[BillTony]

Thanks for such a rapid solution!

This works perfectly for the data in column C - which was indeed the focus of the post.

However, I have a similar dilemma in column A (Fill Down, based on data being present in column B).

The problem here is that the data in column A is a Date (the previous day's date (Date - 1)).

This needs to fill NON-INCREMENTALLY for all the records present for the same daily data set discussed in the original post.

I receive the same 1004 error as previously, and I've included the code I was using for the AutoFill in that column.

Many Thanks!

Selection.AutoFill Destination:=Range(ActiveCell.Address, Cells(Last_Row_ColB, ActiveCell.Column)), Type:=xlFillCopy

 

[Fluff]

How about

    Dim Rng As Range
    Dim UsdRws As Long

    UsdRws = Range("B" & Rows.Count).End(xlUp).Row
    Set Rng = Range("A1").End(xlDown)
    
    Range(Rng, Range("A" & UsdRws)).Value = Rng.Value
    With Range(Rng.Offset(1, 2), Range("B" & UsdRws).Offset(, 1))
        .FormulaR1C1 = "=IF(ISNA(RC[1]),"""",""EXC"")"
    End With