application-defined or object-defined error in cell range visual basic

Question

Having some problems with the code below. I'm getting an Application-defined or object-defined error in the initiation of a second for loop. The format of the second loop range is what seems to be causing the problem. Removing the Sheets() object gets rid of the error, but then the script reads from the wrong worksheet, and doesn't return any data.

The goal of this code is to loop over a vertical array of data, and then if a match to a selection from a dropdown is found, it loops over a horizontal array of data and returns a color change if it finds a 'Yes' value.

If Not Intersect(Target, Range("countryProductCell")) Is Nothing Then
    lastcolumn = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
    Dim cellRow As Integer
    cellRow = Target.Row
    Dim defaultCellColumn As Integer
    defaultCellColumn = 4
    i = 5
    j = 1
    k = 1
    If Not Cells(cellRow, defaultCellColumn).Value = "(Select Title)" Then
        For Each countryCell In Range(Cells(cellRow, defaultCellColumn + 1), Cells(cellRow, lastcolumn))
            If countryCell.Value = "Use Default" Then
                countryCell.Interior.ColorIndex = 3
            End If
        Next
        For Each nameCell In Sheets("Active Product Catalog").Range("ProductNames")
            If nameCell.Value = Cells(cellRow, defaultCellColumn).Value Then
                'Error on the line below!
                For Each purchaseableCell In Sheets("Active Product Catalog").Range(Cells(nameCell.Row, 10), Cells(nameCell.Row, 27))
                    If purchaseableCell.Value = "Yes" Then
                        'If Purchaseable, Change Color
                        Sheets("Home Template").Cells(cellRow, defaultCellColumn + j).Interior.ColorIndex = 35
                    End If
                    j = j + 1
                Next
            End If
            k = k + 1
        Next
    ElseIf Cells(cellRow, defaultCellColumn).Value = "(Select Title)" Then
        If Target.Value = "(Select Title)" Then
            Target.Interior.Color = Cells(Target.Row, Target.Column - 1).Interior.Color
            For Each countryCell In Range(Cells(cellRow, defaultCellColumn + 1), Cells(cellRow, lastcolumn))
                If countryCell.Value = "Use Default" Then
                    countryCell.Interior.ColorIndex = 2
                End If
                i = i + 1
            Next
        ElseIf Target.Value = "Use Default" Then
            Target.Interior.ColorIndex = 2
        ElseIf Application.VLookup(ActiveSheet.Cells(cellRow, Target.Column), Sheets("Active Product Catalog").Range("E:AK"), Target.Column, False) = "Yes" Then
            Target.Interior.ColorIndex = 35
        ElseIf Not Application.VLookup(ActiveSheet.Cells(cellRow, Target.Column), Sheets("Active Product Catalog").Range("E:AK"), Target.Column, False) = "Yes" Then
            Target.Interior.ColorIndex = 3
        End If
    End If
End If

What @steveo40 said, but more broadly speaking, never use Range() or Cells() without a including a qualifying worksheet reference: otherwise you're always relying on what is the currently Active Sheet: this makes your code prone to errors which can be hard to track down. — Tim Williams
– Tim Williams, Commented May 14, 2013 at 20:30

steveo40 · Accepted Answer · 2013-05-14 20:02:44Z

5

You need to qualify the Cells references in your code. The reason it is failing is that you are using two Cell references from one sheet (the active sheet) and asking VBA to define a range in another sheet (Active Product Catalog). Try something like this:

Sheets("Active Product Catalog").Range(Sheets("Active Product Catalog").Cells(nameCell.Row, 10), Sheets("Active Product Catalog").Cells(nameCell.Row, 27))

You'll probably find it a bit easier to read if you create a worksheet object or use a With statement.

answered May 14, 2013 at 20:02

steveo40

9315 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

application-defined or object-defined error in cell range visual basic

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related