CODEWITHSUNDEEP
excelvba
Jishan
Jishan

Reputation: 1684

Object Required in For Each Loop That Loops Through Range

So, I am trying to get the cell address of selected cells based on a For Each loop.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False

    Dim company As String
    company = Range("H12").Value

    Dim companyRange, cell As Range
    companyRange = ThisWorkbook.Sheets("Bleh List").Range("A2:A20")

    For Each cell In companyRange
        If cell.Value <> vbNullString And cell.Value = company Then
                Debug.Print "C : " & cell.Row
        End If
    Next cell
    Application.EnableEvents = True
End Sub

However everytime I try to run this, I am returned a Object required error on the first line of the loop.

What should have been so trivial is creating this problem > why?

Thanks!

Upvotes: 1

Views: 319

Answers (1)

BigBen
BigBen

Reputation: 50006

This is a common misconception:

Dim companyRange, cell As Range

Only cell is a Range. companyRange is a Variant. You need: 

Dim companyRange as Range, cell as Range

Then you're missing a Set.

Set companyRange = ThisWorkbook.Sheets("Bleh List").Range("A2:A20")

Currently, companyRange = ThisWorkbook.Sheets("Bleh List").Range("A2:A20") is a 2D Variant array, not the Range object that you are expecting to iterate over.

Upvotes: 3

Related Questions