Hi Dave,

I replied prior to testing it, the formula is already there, i changed the 5
to 1000, it really works great, thank you for your great work.

"David" wrote:

Hi Dave,

Thank you for taking the time and writting the codes, the formula will
differ, however it could be as simple as +the value in amount column/1000.
can you please help me include that in the code?

"Dave Peterson" wrote:

I'm guessing that you're looking at a specific row (row 1???) that contains
those headers?

And you didn't share what that formula would be!

Option Explicit
Sub testme()

Dim FoundCell As Range
Dim wks As Worksheet
Dim myStr As String
Dim LastRow As Long

myStr = "Amount"

Set wks = Worksheets("Sheet1")

With wks
'formula will fill rows 2 to the last used cell
'in column A
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Rows(1)
Set FoundCell = .Cells.Find(What:=myStr, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox myStr & " wasn't found!"
Else
.Cells(1, FoundCell.Column + 1).EntireColumn.Insert
.Cells(1, FoundCell.Column + 1).Value = "New Column"
.Range(.Cells(2, FoundCell.Column + 1), _
.Cells(LastRow, FoundCell.Column + 1)).FormulaR1C1 _
= "=rc[-1]/5"
End If

End With
End Sub



David wrote:

Hi,

I am trying to create a macro that will add a column after a specific text
and title the column add formula,

example, i have a sheet with 50 columns one of which is titled "Amount" i
want the macro find that title and add a column to the right of it and title
it "new column" and add a formula.

really appreciate any help.

thanks
David

--

Dave Peterson