FindTextInsertCol Excel

[Langdon]

How do I insert a column after a specific text is found in the first row
(i.e. the number 1) for every time it appears in a worksheet?

 

[Bernie Deitrick]

Langdon,

You would need to loop like in the example below.

HTH,
Bernie
MS Excel MVP


Sub InsertColumnsAfter1()
Dim FirstAdd As String
Dim C As Range

If Range("A1").Value = 1 Then
Set C = Range("A1")
Else
Set C = Rows("1:1").Find(What:="1", LookAt:=xlWhole)
End If

If Not C Is Nothing Then
FirstAdd = C.Address
C.Offset(0, 1).EntireColumn.Insert
Else
Exit Sub
End If
Set C = Rows("1:1").FindNext(after:=C)
While C.Address <> FirstAdd
C.Offset(0, 1).EntireColumn.Insert
Set C = Rows("1:1").FindNext(after:=C)
Wend

End Sub

 

[Rick Rothstein]

Here is an alternate macro that does the same thing...

Sub InsertColumnsAfter1()
Dim X As Long, R As Range
For X = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
If Cells(1, X).Value = 1 Then
If R Is Nothing Then
Set R = Columns(X)
Else
Set R = Union(R, Columns(X))
End If
End If
Next
R.Offset(0, 1).Insert
End Sub