Autofill Range keeps giving me an error

[Olio1225]

So I've been trying to get my vba code to autofill a column. The 2nd cell in that column has a formula.

Dim nextEmptyColumn As Range

Set nextEmptyColumn = Worksheets("Sheet1").Range("A1").End(xlToRight).Offset(0, 1)

nextEmptyColumn.Value = "AlertKey"

nextEmptyColumn.Offset(1).Formula = "=(MID(Sheet1!D2,FIND(""AlertKey:"",Sheet1!D2)+LEN(""AlertKey:""),FIND(""AlertGroup"",Sheet1!D2)-FIND(""AlertKey:"",Sheet1!D2)-LEN(""AlertKey:"")))"

nextEmptyColumn.Offset(1).AutoFill Range()

I keep getting errors when I try to do range. Any know how to autofill from the second row in the column to the last row with data in the adjacent column.

 

[Alex Blakenburg]

See if this helps:

Sub test()

    Dim ws As Worksheet
    Dim nextEmptyColumn As Range

    
    Set ws = Worksheets("Sheet1")
    
    With ws
        Set nextEmptyColumn = .Range("A1").End(xlToRight).Offset(0, 1)
        nextEmptyColumn.Value = "AlertKey"
        nextEmptyColumn.Offset(1).Formula = "=(MID(Sheet1!D2,FIND(""AlertKey:"",Sheet1!D2)+LEN(""AlertKey:""),FIND(""AlertGroup"",Sheet1!D2)-FIND(""AlertKey:"",Sheet1!D2)-LEN(""AlertKey:"")))"
        
        'Autofill Lines
        Dim lastRowAdjacentCol As Long
        lastRowAdjacentCol = .Cells(Rows.Count, nextEmptyColumn.Offset(0, -1).Column).End(xlUp).Row
        nextEmptyColumn.Offset(1).AutoFill Destination:=.Range(nextEmptyColumn.Offset(1), .Cells(lastRowAdjacentCol, nextEmptyColumn.Column))
    End With
    
End Sub

 

[Olio1225]

See if this helps:

Sub test()

    Dim ws As Worksheet
    Dim nextEmptyColumn As Range

   
    Set ws = Worksheets("Sheet1")
   
    With ws
        Set nextEmptyColumn = .Range("A1").End(xlToRight).Offset(0, 1)
        nextEmptyColumn.Value = "AlertKey"
        nextEmptyColumn.Offset(1).Formula = "=(MID(Sheet1!D2,FIND(""AlertKey:"",Sheet1!D2)+LEN(""AlertKey:""),FIND(""AlertGroup"",Sheet1!D2)-FIND(""AlertKey:"",Sheet1!D2)-LEN(""AlertKey:"")))"
       
        'Autofill Lines
        Dim lastRowAdjacentCol As Long
        lastRowAdjacentCol = .Cells(Rows.Count, nextEmptyColumn.Offset(0, -1).Column).End(xlUp).Row
        nextEmptyColumn.Offset(1).AutoFill Destination:=.Range(nextEmptyColumn.Offset(1), .Cells(lastRowAdjacentCol, nextEmptyColumn.Column))
    End With
   
End Sub

Yup It worked perfectly. Thank you!

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.