Method 'Range' of object '_Global' failed error

[Ilix]

Hey everyone!

I'm currently working on a project where I need to create 5 tables (one for each business day) and populate them with data from another page in the same worksheet. Right now I'm doing this by simply adding rows to the tables that already exist and already have VLookups set up the way I need them. The new rows I'm adding have one of the following happen:

• The VLOOKUP formula is correctly copied (rare)
• Nothing is copied down
• Completely unrelated formulas are copied down (I have no idea where they are coming from)
• Outdated VLOOKUP formulas as copied down (they no longer exist anywhere on the sheet and the named range they refer to has been deleted)

To get around this I'm trying to copy the formatting down in VBA so I can call the loop when the tables are created. Here is the code I'm using:

    Dim f As Integer 
    Dim l As Integer 
    Dim Filler As Integer 




    l = 0
    For f = 1 To 5
        Range("C4").Select
        ActiveCell.Offset(0, l).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Resize(Selection.Rows.Count - 1).Select
'        Set Filler = ActiveSheet.Range(Selection.Address)
        Filler = Selection.Rows.Count
        Range(Cells(4, 3 + l)).AutoFill Destination:=Range(Cells(4, 3 + l), Cells(3 + Filler, 3 + l)), Type:=xlFillDefault
        l = l + 7
    Next f

I'm getting the Run-time error '1004': Method 'Range' of object '_Global' failed when it hits the "Range(Cells(4, 3 + l)).AutoFill Destination:=Range(Cells(4, 3 + l), Cells(3 + Filler, 3 + l)), Type:=xlFillDefault" line.

I need to either figure out why the formulas aren't copying down when adding lines to the table, or figure out how to get this fill working. Since the number of rows won't always been the same, and since there is a 'total' row at the bottom of the table, I can't just copy the rows down X number of cells.

In case it's helpful, below is the code I'm using to add rows to the tables. If I can resolve the issue here rather than creating a new loop to call, that would be great as well:

    For i = 1 To 5
        
        Dim R As Integer 
        Dim j As Integer 


        For R = 1 To TotalRows
            j = R + 1 'This calculates the correct offset for filling data
            Application.Goto Reference:="Table" & i
            Selection.End(xlUp).Select
            ActiveCell.Offset(j, 0).Select
            Selection.ListObject.ListRows.Add
            ActiveCell.Value = featureArray(R, 1)
        Next R
    Selection.ListObject.ListRows(TotalRows + 2).Delete
    
    Next i

I'm learning while working on this thing, so I really appreciate any help anyone can provide. I'd also love an explanation of what the error means so I can better try to resolve it in the future. In looking for help on Google, I've seen a lot of people get the same error with similar, but not the same, issues and I don't want to have to ask for help any time it pops up.

 

[pbornemeier]

First recommendation:
Do not use lowercase l as a variable. it looks a lot like a one (1) in the VBE default editing text!

The reason you are getting the Run-time error is that this is not valid:
Range(Cells(4, 3 + l)).Autofill
try this:
Cells(4, 3 + l).Autofill

Here is an alternate procedure for your first block of code:

    Dim f As Integer
    Dim lColumnOffset As Long
    Dim lLastActiveColumnDataCell As Long

    lColumnOffset = 0
    For f = 1 To 5
        lLastActiveColumnDataCell = Cells(Rows.Count, 3 + lColumnOffset).End(xlUp).Row - 1 '1 less than total row
        With Range(Cells(4, 3 + lColumnOffset), Cells(lLastActiveColumnDataCell, 3 + lColumnOffset))
            .FormulaR1C1 = Cells(4, 3 + lColumnOffset).FormulaR1C1
        End With
        lColumnOffset = lColumnOffset + 7
    Next f

Run Time Error 1004 is a kind of generic error read it like "something is wrong, but I can't say what" Click help when the error is raised, and read that page for more info.