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

  • Thread starter Thread starter David
  • Start date Start date
D

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
 
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
 
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 Don,

You are great, Thank you

Don Guillett said:
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)
Click to expand...
 
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top