Insert column based on cell string value

[CluelessAboutExcel]

Hi,

I have been working on a VBA formula to insert columns to a table based on an integer entry in one cell. So far I have managed to get it to work but as there are multiple categories of columns to add, I need to input the column numbers in a certain order as the columns are inserted based on excel column indexes (The columns will insert at the same place every time which could be wrong if there have already been new columns added). I want to code it so that VBA looks up a certain string value in a cell across a row and enters the new column with respect to that. After this, I want to name the new inserted columns with an increasing category number (i.e. country1, country2, etc.) This is what I have got so far:

Sub Add_Column()

    Dim aCount As Integer
    Dim a As Integer
    Dim bCol As Long
    Dim b As Long

    aCount = Range("B12").Value
    bCol = Cells(15, Columns.Count).End(xlToLeft).Column

    For a = 1 To aCount - 1
        For b = 1 To bCol
            Range(Cells(15, b).Value = "Category 1").Offset(0, 1).Insert Shift:=xlToRight
            Range("G15").Value = "Category" + Str(aCount - a + 1)
        Next b
    Next a

End Sub

The problem is with the line using the range as cells with category 1 as it comes up with a "Method 'Range' of object '_Global' failed" error message but I couldn't figure out how else to put a string value of a cell in a row into the range expression.

In addition to this, the line below naming the new column has a range which is relative to the new column input position (G15), if this could be done more efficiently that would be useful to know!

Any help would be massively appreciated!

 

[CluelessAboutExcel]

I have tried setting the range as an object which finds "Category 1" but it is coming up witht he error "Object doesn't support this property or method". See below what I have attempted if it is more along the right lines.

Sub Add_Column()

    Dim aCount As Integer
    Dim a As Integer
    Dim bCol As Long
    Dim b As Long
    Dim c As Range

    aCount = Range("B12").Value
    bCol = Cells(15, Columns.Count).End(xlToLeft).Column

    With Worksheets(3)
        Set c = .Find("Category 1", LookIn:=xlValues)

        For a = 1 To aCount - 1
            For b = 1 To bCol
                Range(Cells(15, b).Value = "Category 1").Offset(0, 1).Insert Shift:=xlToRight
                Range("G15").Value = "Category" + Str(aCount - a + 1)
            Next b
        Next a
    End With

End Sub
[/COD

 

[CluelessAboutExcel]

I have tried setting the range as an object which finds "Category 1" but it is coming up witht he error "Object doesn't support this property or method". See below what I have attempted if it is more along the right lines.

Sub Add_Column()

    Dim aCount As Integer
    Dim a As Integer
    Dim bCol As Long
    Dim b As Long
    Dim c As Range

    aCount = Range("B12").Value
    bCol = Cells(15, Columns.Count).End(xlToLeft).Column

    With Worksheets(3)
        Set c = .Find("Category 1", LookIn:=xlValues)

        For a = 1 To aCount - 1
            For b = 1 To bCol
                Range(Cells(15, b).Value = "Category 1").Offset(0, 1).Insert Shift:=xlToRight
                Range("G15").Value = "Category" + Str(aCount - a + 1)
            Next b
        Next a
    End With

End Sub
[/COD

I meant to say that I used

c.Offset

instead of

Range(Cells(15, b).Value = "Category 1")

in the above code

 

[CluelessAboutExcel]

UPDATE:
I want to create a VBA code so that I can look up the string "Category 1" and use it as a reference to add a certain number of columns based on another cells value. The new cell values are to have the headings "Category 2", "Category 3", etc.

Currently, I have used the .Find function to locate "Category 1" and then offset columns from there using the .find as a range (see below). However, when I run the macro nothing changes to my spreadsheet. I was wondering whether anybody could spot what is happening here!

Any help would be much appreciated.

See the code below:

Sub Add_Column()

Dim aCount As Integer
Dim a As Integer
Dim c As Range

With Worksheets(3)
aCount = .Range("B12").value
set c = .Cells.Find("Category 1", LookIn:=xlvalues)

For a = 1 To aCount - 1
c.Offset(0, 1).Insert Shift := xlToRight
.Range("G15").Value = "Category" & Str(aCount - a + 1)
Next a

End With

End Sub