Error with a mix of .range.cells and with block

[YounesB3]

Hi, I'm having an error at the highlighted ConsoRow line throwing this error: "object variable or with block not set".

I tried many different things, but to no avail. Help please.

Sub Conso_structure()


Dim i As Long, j As Long, k As Long, LevelMax As Long, LastrowInput As Long, LastrowComb As Long, LastrowConso As Long, CombinedRow As Long, ConsoRow As Long
Dim Color As Single
Dim Leaf As String, Description As String, Parent As String
Dim LevelCount As Object
Set LevelCount = CreateObject("Scripting.Dictionary")


LastrowInput = Worksheets("Input").Range("A" & Rows.Count).End(xlUp).Row


Worksheets("Consolidated structure").Cells.Clear
With Worksheets("Consolidated structure").Cells.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With


With Worksheets("KSH3 Combined")


    LastrowComb = .Range("A" & Rows.Count).End(xlUp).Row
    LevelMax = Application.WorksheetFunction.Max(.Range(.Cells(1, 3), .Cells(LastrowComb, 3)))
    
    For i = 1 To LevelMax
        LevelCount(i) = Application.CountIf(.Range(.Cells(1, 3), .Cells(LastrowComb, 3)), i)
    Next i


End With


With Worksheets("Consolidated structure")
    With .Range(.Cells(1, 1), .Cells(1, LevelMax))
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .MergeCells = True
        .Font.Bold = True
        .Font.Size = 18
        .Font.ThemeColor = xlThemeColorDark1
        .Font.TintAndShade = 0
        .Interior.Pattern = xlSolid
        .Interior.PatternColorIndex = xlAutomatic
        .Interior.ThemeColor = xlThemeColorLight1
        .Interior.TintAndShade = 0
        .Interior.PatternTintAndShade = 0
        .Value = "CPSGATOTAL-TOTAL SG&A"
    End With
End With


Color = -0.1
    
For j = 2 To LevelMax


    CombinedRow = Worksheets("KSH3 Combined").Range("C1:C" & LastrowComb).Find(What:=j, After:=Worksheets("KSH3 Combined").Range("C1"), _
        LookIn:=xlValues, LookAt:=xlPart, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row


    For k = 1 To LevelCount(j)
        
        Leaf = Worksheets("KSH3 Combined").Cells(CombinedRow, 3).Offset(0, -2).Value
        Description = Worksheets("KSH3 Combined").Cells(CombinedRow, 3).Offset(0, -1).Value
        Parent = Worksheets("KSH3 Combined").Cells(CombinedRow, 3).Offset(0, 1).Value
        
        With Worksheets("Consolidated structure")
            LastrowConso = .Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
[COLOR=rgb(243, 121, 52)]            ConsoRow = .Range(.Cells(1, 1), .Cells(LastrowConso, LevelMax)).Find(What:=Parent, After:=.Range(.Cells(1, j - 1), .Cells(1, j - 1)), _
                LookIn:=xlValues, LookAt:=xlPart, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row[/COLOR]
            .Rows(ConsoRow).EntireRow.Insert
            .Rows(ConsoRow).EntireRow.Insert
            .Range(.Cells(ConsoRow, j), .Cells(ConsoRow, LevelMax)).HorizontalAlignment = xlLeft
            .Range(.Cells(ConsoRow, j), .Cells(ConsoRow, LevelMax)).MergeCells = True
            .Range(.Cells(ConsoRow, j), .Cells(ConsoRow, LevelMax)).Interior.Pattern = xlSolid
            .Range(.Cells(ConsoRow, j), .Cells(ConsoRow, LevelMax)).Interior.PatternColorIndex = xlAutomatic
            .Range(.Cells(ConsoRow, j), .Cells(ConsoRow, LevelMax)).Interior.ThemeColor = j + 3
            .Range(.Cells(ConsoRow, j), .Cells(ConsoRow, LevelMax)).Interior.TintAndShade = Color
            .Range(.Cells(ConsoRow, j), .Cells(ConsoRow, LevelMax)).Interior.PatternTintAndShade = 0
            .Range(.Cells(ConsoRow, j), .Cells(ConsoRow, LevelMax)).Value = Leaf & "-" & Description
            .Range(.Cells(ConsoRow, LevelMax + 1), .Cells(ConsoRow, LevelMax + 1)).Value = Application.VLookup(Leaf, _
                    Worksheets("Input").Range("A1:F" & LastrowInput), 3, False)
            .Range(.Cells(ConsoRow, LevelMax + 2), .Cells(ConsoRow, LevelMax + 2)).Value = Application.VLookup(Leaf, _
                    Worksheets("Input").Range("A1:F" & LastrowInput), 4, False)
            .Range(.Cells(ConsoRow, LevelMax + 3), .Cells(ConsoRow, LevelMax + 3)).Value = Application.VLookup(Leaf, _
                    Worksheets("Input").Range("A1:F" & LastrowInput), 5, False)
            .Range(.Cells(ConsoRow, LevelMax + 4), .Cells(ConsoRow, LevelMax + 4)).Value = Application.VLookup(Leaf, _
                    Worksheets("Input").Range("A1:F" & LastrowInput), 6, False)
        End With
        
        CombinedRow = CombinedRow + 1
        
    Next k


    Color = Color + 0.1
    
Next j

 

[Akuini]

I'm having an error at the highlighted ConsoRow line throwing this error: "object variable or with block not set".

It means that the Find method didn't find anything.
You should check if it does find something, like this:

With Worksheets("Consolidated structure")
    LastrowConso = .Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    Dim c As Range
    Set c = .Range(.Cells(1, 1), .Cells(LastrowConso, LevelMax)).Find(What:=Parent, After:=.Range(.Cells(1, j - 1), .Cells(1, j - 1)), _
                LookIn:=xlValues, LookAt:=xlPart, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not c Is Nothing Then
        ConsoRow = c.Row
        .Rows(ConsoRow).EntireRow.Insert
        .....
        .....
        .Range(.Cells(ConsoRow, LevelMax + 4), .Cells(ConsoRow, LevelMax + 4)).Value = Application.VLookup(Leaf, _
         Worksheets("Input").Range("A1:F" & LastrowInput), 6, False)
    End If
End With

 

[Zot]

To get the row for ConsoRow you use find, but find results in range. You have to use Set.

 

[Fluff]

To get the row for ConsoRow you use find, but find results in range. You have to use Set.

ConsoRow is declared as long, so you don't use Set & whilst find does result in a range the code has .Row after the find so it's returning a number.
As Akuini has said It's probably not finding anything.

 

[Zot]

ConsoRow is declared as long, so you don't use Set & whilst find does result in a range the code has .Row after the find so it's returning a number.
As Akuini has said It's probably not finding anything.

I was over simplified in my statement. I meant to say that using Find results in a range and need to use Set statement. That is what error say I guess. It was not meant for ConsoRow.

Then I saw Akuini response with solution before I go on writing more

 

[Fluff]

I meant to say that using Find results in a range and need to use Set statement.

And that is what I was saying is wrong.

 

[YounesB3]

Yup, Akuini got it right. I made a small change which I forgot about. Thanks!

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.