Find word insert formula

[marreco]

Bom dia!!

Hi.
I need find a word (all columns in row 1), after (below row 2), insert fomula.

Cross-Post
http://www.excelforum.com/search.php?searchid=2913371

Sub Find_InsertFormula() 
    Dim lr As Long
    'Call Find_First
    Application.ScreenUpdating = False

    With Sheets("Plan1")
        lr = .Cells(.Rows.Count, "A").End(xlUp).Row - 1
        .ActiveCell.Formula = "=1+1+1"
        .ActiveCell.AutoFill .ActiveCell.Resize(lr - 1)
    End With
    Application.ScreenUpdating = True End Sub

Sub Find_First()    
Dim FindString As String
    Dim Rng As Range
    FindString = "Atual"
    If Trim(FindString) <> "" Then
        With Sheets("Plan1").Range("A1:Q1")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng.Offset(2, 1), True
            Else
                MsgBox "Não encontrado"
            End If
        End With
    End If End Sub

Att

[mancubus]

I need find a word (all columns in row 1), after (below row 2), insert fomula.

hi.

try this:

Sub Find_Insert()    Dim Rng As Range
    Dim LastRow As Long
    With Sheets("Plan1")
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set Rng = .Rows(1).Find("Atual")
        If Not Rng Is Nothing Then
            .Range(.Cells(3, Rng.Column), .Cells(LastRow, Rng.Column)).Formula = "=1+1+1"
        Else
            MsgBox "Não encontrado"
        End If
    End With
End Sub

[marreco]

Hi

Very very good, I'll do the test, then topic as solved.

thank you very much!!!

[marreco]

Hi.

Thank you!!!

[mancubus]

you're welcome.

if you're sure the word "Atual" exists in row 1, it can be done with a one liner.

Sub Find_Insert_2()
    With Sheets("Plan1")
        .Rows(1).Find("Atuals").Offset(2).Resize(.Cells(.Rows.Count, "A").End(xlUp).Row - 2).Formula = "=1+1+1"
    End With
End Sub

if "Atual" does not exist in row 1, above code will throw RTE 91, "Object variable or With block variable not set" error.

[marreco]

Hi. mancumbus, thank you!