CODEWITHSUNDEEP
excelvba
VBAnoob
VBAnoob

Reputation: 163

Excel VBA Finding a string and inserting a column on loop

I have a table that has headers that can go from P1 to P# (whatever that number is).

I want to build a macro that starts out by finding P2 and inserts a column and will continue doing this for all the P's(until it hits P#). It will then take those created columns and make the cells width: 6. I reused a formula to find the cell, but I don't know where to go from there.

'Find P1 in sheet 1

SearchString = "P2"
    Application.FindFormat.Clear
            ' loop through all sheets
            For Each sh In ActiveWorkbook
                    'Find first instance on sheet
                    Set cl = sh.Cells.Find(What:=SearchString, _
                    After:=sh.Cells(1, 1), _
                    LookIn:=xlValues, _
                    LookAt:=xlPart, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False, _
                    SearchFormat:=False)
                If Not cl Is Nothing Then
                    selectcell
                    ' if found, remember location
                  With ActiveCell.insertcolumn
                End If
            Next

How do I make this formula select and insert a column, then do it on repeat for all Ps?

Upvotes: 1

Views: 676

Answers (1)

DiegoAndresJAY
DiegoAndresJAY

Reputation: 706

This assumes your first sheet has the headers you are looking to use as a baseline in the first row. This will insert new columns to the right of the existing column and resize it.

Public Sub addColumns()
    Dim intHeaderRow as Integer
    intHeaderRow = 1
    For i = sheets(1).usedrange.columns.count to 1 step -1
        addColumn(sheets(1).cells(intHeaderRow, i))
    Next
End Sub

Public Sub addColumn(byval SearchString as string)
    Dim intColumnFound as integer

    Application.FindFormat.Clear
    ' loop through all sheets
    For Each sh In Worksheets
        'Find first instance on sheet
        Set cl = sh.Cells.Find(What:=SearchString, _
        After:=sh.Cells(1, 1), _
        LookIn:=xlValues, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False)
        If Not cl Is Nothing Then
            intColumnFound = cl.Column
            sh.Columns(intColumnFound + 1).Insert
            ' if found, remember location
            sh.Columns(intColumnFound + 1).ColumnWidth = 6
        End If
    Next
End Sub

You had a comment to "remember location" when the value was found. I'm not sure if you needed that, so I stored it in intColumnFound, but it will be overwritten on the next sheet and not used in this code.

Upvotes: 2

Related Questions