VBA: Insert 2 columns if header matches

Question

I need to insert 2 columns if the header contains "*FFT Target". I've found this code, however, it does not move on to the next column containing "FFT Target" but inserts the two rows before the first column where the heading matched.

Sheet headers I currently have are:

English FFT Target English Teacher Assessment English EFG Maths FFT Target Maths Teacher Assessment Maths EFG

What I need is

[blank column] [blank column] English FFT Target English Teacher Assessment English EFG [blank column] [blank column] Maths FFT Target Maths Teacher Assessment Maths EFG

The code I have is:

Dim A As Range
Dim lc As Long
Dim i As Long

Set A = Rows(1).Find(what:="*Target", LookIn:=xlValues, lookat:=xlPart)
lc = Cells(1, Columns.Count).End(xlToLeft).Column

For i = 2 To lc

        If A Is Nothing Then Exit Sub
        A.Resize(, 2).EntireColumn.Insert
Next i

Unfortunately, this code inserts all the columns before English FFT Target rather than moving on and inserting columns before the next column containing FFT Target.

Any help would be greatly appreciated.

Thanks

You need a loop. A returns a single range and is never updated. Also, please post a screenshot as your layout is not clear from your question. — SJR
– SJR, Commented Feb 28, 2019 at 11:16

Error 1004 · Accepted Answer · 2019-02-28 11:40:54Z

2

i think this could helps you:

Option Explicit

Sub Insert()

    Dim LastColumn As Long, i As Long, Position As Long

    With ThisWorkbook.Worksheets("Sheet1")

        'Get the last column of Sheet 1, row 1
        LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column

        For i = LastColumn To 1 Step -1

            Position = InStr(1, .Cells(1, i), "FFT Target")

            If Position <> 0 Then

                .Range(.Cells(, i), .Cells(, i + 1)).EntireColumn.Insert

            End If

        Next i

    End With

End Sub

edited Feb 28, 2019 at 11:40

answered Feb 28, 2019 at 11:34

Error 1004

8,2883 gold badges36 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Siddharth Rout Over a year ago

++ for posting a similar solution before me :D However you can shorten your code...

Error 1004 Over a year ago

Really appreciate your tips!

Fazila Over a year ago

Thanks for posting Error 1004 unfortunately didn't work I think the issue was finding FFT Target it needs to match part of the cell rather than the whole cell.

Siddharth Rout Over a year ago

@Fazila: What happens if you change InStr(1, .Cells(1, i), "FFT Target") to InStr(1, .Cells(1, i).Value, "FFT Target", vbTextCompare)

Siddharth Rout Over a year ago

@Fazila: or InStr(1, .Cells(1, i).Value, "Target", vbTextCompare) in case FFT is not that important...

SJR · Accepted Answer · 2019-02-28 11:50:29Z

In case you want to stick with Find. As you are inserting columns, you need a bit of jiggery-pokery to check you are not recycling previously found values, and you need to set the search direction. Setting other parameters is also good practice.

However, I think Error 1004's approach makes more sense here.

Sub x()

Dim A As Range
Dim lc As Long
Dim i As Long
Dim s As String

Set A = Rows(1).Find(What:="Target", after:=Cells(1, 1), LookIn:=xlValues, LookAt:=xlPart, _
                     SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)

If Not A Is Nothing Then
    s = A.Address
    Do
        A.Resize(, 2).EntireColumn.Insert
        s = Range(s).Offset(, 2).Address
        Set A = Rows(1).FindNext(A)
    Loop Until A.Address = s
End If

End Sub

Collectives™ on Stack Overflow

VBA: Insert 2 columns if header matches

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related