Insert Column after specific text title the new column and add for
 

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

Insert Column after specific text title the new column and add for
 

Modify to suit. Assumes Amount is in the FIRST row and you want formula just
below insertion.
Sub FindTextInsertCol()
mc = Rows("1").Find(What:="Amount", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False).Column
Columns(mc + 1).Insert
Cells(2, mc + 1).Formula = "=a1*2"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"David" wrote in message
...
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

Insert Column after specific text title the new column and add for
 

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

Insert Column after specific text title the new column and add
 

Hi Don,

You are great, Thank you

"Don Guillett" wrote:

Modify to suit. Assumes Amount is in the FIRST row and you want formula just
below insertion.
Sub FindTextInsertCol()
mc = Rows("1").Find(What:="Amount", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False).Column
Columns(mc + 1).Insert
Cells(2, mc + 1).Formula = "=a1*2"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"David" wrote in message
...
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

Insert Column after specific text title the new column and add
 

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

Insert Column after specific text title the new column and add
 

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