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

YounesB3

Board Regular
Joined
Mar 28, 2012
Messages
138
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.

VBA Code:
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
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:
Rich (BB code):
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
 
Solution

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,174
Office Version
  1. 2016
Platform
  1. Windows
To get the row for ConsoRow you use find, but find results in range. You have to use Set.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,814
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,174
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,814
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Mar 28, 2012
Messages
138
Yup, Akuini got it right. I made a small change which I forgot about. Thanks! :)
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help & thanks for the feedback. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,081
Messages
5,639,961
Members
417,120
Latest member
Pavithra devi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top