Using .Find as a range in VBA-CodePudding

I want to search along a row to find a string cell entry "Category 1" and insert a certain number of columns (based on another cell value) from this point. The new columns should then have labels "Category 2", "Category 3", etc.

So far I have managed to code it up to be able to do the above based on specific column indexes (not string cell entries). However, I have been trying to modify it so that I can use "Category 1" as a reference point for column insertion. My below code is my attempt at this but comes up with "Object doesn't support this property or method". When looking on microsoft's advice page on the .Find function it has very similar code to what I have used so I am really stumped on what is going wrong.

Any help would be massively appreciated!

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
                c.Offset(0, 1).Insert Shift:=xlToRight
                Range("G15").Value = "Category"   Str(aCount - a   1)
            Next b
        Next a
    End With

End Sub

CodePudding user response：

A Worksheet has no Find-method, a Range has. So the command should look like this:

Set c = .Cells.Find("Category 1", LookIn:=xlValues)

(Cells is a Range containing all cells of a worksheet)

I don't understand all the details of your code logic, but I assume that Range("B12") and Range("G15") are on Worksheets(3)? You should qualify all Ranges (tell Excel which worksheet you are referring to (else the ActiveSheet is used and that is not always what you want). And use & for string concatenation, not

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

    Set c = .Cells.Find("Category 1", LookIn:=xlValues)

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

CodePudding user response：

Maybe this works.

Sub x()

Dim aCount, bCol As Long, c As Range, a As Long

With Worksheets(3)
    aCount = .Range("B12").Value
    If Not IsNumeric(aCount) Then Exit Sub
    
    bCol = .Cells(15, .Columns.Count).End(xlToLeft).Column

    Set c = .Cells.Find("Category 1", LookIn:=xlValues)
    If c Is Nothing Then Exit Sub
    
    For a = 1 To aCount - 1
        c.Offset(0, a).Insert Shift:=xlToRight
        c.Offset(0, a).Value = "Category " & a   1
    Next a
End With

End Sub