PDA

View Full Version : [SOLVED] Find word insert formula



marreco
05-23-2014, 12:27 PM
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
05-23-2014, 01:29 PM
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
05-23-2014, 01:39 PM
Hi

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

thank you very much!!!

marreco
05-23-2014, 05:31 PM
Hi.

Thank you!!!

mancubus
05-24-2014, 12:36 AM
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
05-24-2014, 08:08 AM
Hi. mancumbus, thank you!