CODEWITHSUNDEEP
vbaexcel
Jaan
Jaan

Reputation: 17

Excel VBA - Autofill formula to the end of the document from one below the cell that is found

I would like help finding out the appropriate code to get AutoFill for my formula in column K to work from one cell under the one that is found, all the way to the last row of the document. How can this be achieved?

Thank you!

Dim s As String
Dim rCell As Range
Dim lReply As Long
Dim firstaddress As String
Dim rngOriginal As Range
Dim Cell As Range

Columns("K:K").Select
Set Cell = Selection.Find(What:="Add", After:=ActiveCell, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
If Not Cell Is Nothing Then
    firstaddress = Cell.Address
    Cell.Offset(0, -6).Insert shift:=xlDown
    Cell.Offset(0, -7).Insert shift:=xlDown
    Cell.Offset(0, -8).Insert shift:=xlDown
    Cell.Offset(0, -9).Insert shift:=xlDown
    Cell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = _
    "Add. "
Range("K9").AutoFill Destination:=Range("K9:K1936"), Type:=xlFillDefault
Cell.Select
ActiveCell.FormulaR1C1 = _
    ""

Upvotes: 0

Views: 42

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149305

No need to use Autofill :) You can input formula in one go!

Like this (UNTESTED)?

Dim ws As Worksheet
Set ws = Sheet1 '<~~ Change as applicable

'
' ~~> Rest of your code
'
With ws '<~~ This is your worksheet object
    LRow = .Range("K" & .Rows.Count).End(xlUp).Row

    .Range("K9:K" & LRow).Formula = .Range("K9").Formula
End With
'
' ~~> Rest of your code
'

Upvotes: 1

SJR
SJR

Reputation: 23081

Think you could have found this out yourself as it's a common VBA question. I've also removed the Selects from your code which are generally unnecessary and inefficient.

Sub x()

Dim s As String
Dim rCell As Range
Dim lReply As Long
Dim firstaddress As String
Dim rngOriginal As Range
Dim Cell As Range
Dim n As Long

Set Cell = Columns("K:K").Find(What:="Add", LookIn:=xlValues, _
                 LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                 MatchCase:=False, SearchFormat:=False)
If Not Cell Is Nothing Then
    firstaddress = Cell.Address
    Cell.Offset(0, -9).Resize(, 4).Insert shift:=xlDown
    Cell.Offset(0, 1).Value = "Add. "
    n = Range("K" & Rows.Count).End(xlUp).Row
    Range("K9").AutoFill Destination:=Range("K9:K" & n), Type:=xlFillDefault
    Cell.Value = 1
End If

End Sub

Upvotes: 0

Related Questions