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

[David]

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

 

[Don Guillett]

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

 

[Dave Peterson]

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]

Hi Don,

You are great, Thank you

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
(e-mail address removed)

 

[David]

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?

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

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

 

[David]

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.

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?

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

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