Insert Columns after find

[arvarr]

Hi
Can someone assist in some code?

Column A Column B Column C Column D
Row1 Apple Banana Apple Banana

I need a code that insert one column after every Banana.

Thanks.

 

[SkipVought]

I assume that the row in the new column will have a fruit value, like coconut.

Becuase in many instances, INSERT & DELETE causes broken refernce problems, I try to avoid using these.

Rather, enter the value(s) in the next available cell(s) and SORT, either vertically or horizontally, achieving the same result. Of course, in your case you will also have a grouping value.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Hi SkipVought
Apple and Bananas are the Header.

So, I wanted to insert columns besides all bananas so that the additional columns can be called coconut.

Thanks.

 

[SkipVought]

Then it should work!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I asume that you already have data in the Apples/Bananas columns, otherwise the solution is even simpler.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Had to get to a PC that has Excel. If you really must use insert, being made aware that there may be adverse consequences...

Sub InsertCol()
    Dim rFound As Range, iPrevCol As Integer
    
    Set rFound = Rows(1).Find("Banana")
    
    Do While iPrevCol < rFound.Column
        With rFound
            .Offset(0, 1).EntireColumn.Insert xlRight
            .Offset(0, 1).Value = "Coconut"
        End With
        iPrevCol = rFound.Column
        Set rFound = Rows(1).Find("Banana", rFound)
    Loop
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Hi Skip
The code above inserts columns but only continuously inserts columns besides column B (Banana).

It does not find the next Banana header in column D to insert columns.

Thanks.

 

[SkipVought]

Huh?

My paradigm: Data in a table on a sheet is contiguous, tables have one row of headings, all headings are unique, no other data below a table.

So what are you referring to?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Sorry Skip, the headings are not unique.

There are similar headings. E.g. Bananas will appear 50 times. So, I would need to insert columns 50 times besides every headers that contains Bananas.

Thanks.

 

[SkipVought]

My procedure looks in ROW 1 for ANY occurrence of Banana and inserts a column.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Sorry Skip, I missed something. Its working perfectly fine now. Thanks for all your help.

 

[SkipVought]

What was missed?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Hi Skip
Sorry for the late response.
I actually missed out that the another column had Bananas instead of Banana.

So, it is also inserting column besides Bananas.

I changed it to an exact find so that it only find Banana without the "s".

Thanks once again for all your help.

 

[SkipVought]

Use Exact Match property in the Find function. Check help.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}