For Each loop not working

[ebraatz]

I'm trying to write a macro that cycles through each cell in a range. I've done this before so I'm not sure why this isn't working? I was getting a type mismatch error on the cell.value line, tried cell.text and got null. Added the Msgbox and it returns the address for the entire range, not just a singular cell in the range. What am I missing?

Sub Create_List ()

Dim cell as Range

For Each cell in RedList.Columns(1)    'This is further defined in my project, trying to simplify here.

    Msgbox cell.Address

    If cell.Value = "2" Then

        'code if true
    End If

Next Cell

End Sub

 

[Norie]

Probably not a good idea to use Range as the name of a variable, especially when you it's an Excel VBA object data type.

Anyways, how exactly are you setting the 'Range' you are trying to loop through?

 

[MrTeeny]

It might also depend on what other data is in the range if you're trying to compare it to 2, maybe try converting the value to Integer, Long etc whatever the data value should be by using, if there's text in the range you'll get mismatches so you might need more error trapping



If cInt(cell.Value) = "2" Then

or


If cLng(cell.Value) = "2"



edit only just noticed you're declaring cell as a range, surely that should be some kind of number as Norie suggested

 

[Jonmo1]

RedList.Columns(1) is a collection of 'Columns', not cells (granted it's only 1 column) but anyway..

So this
For Each cell in RedList.Columns(1)
is essentially saying
For Each Column in RedList.Columns(1)

So Cell becomes a Column, not a Cell

Try
For Each cell in RedList.Columns(1).Cells

 

[ebraatz]

Thanks, Jonmo! That fixed it! Thank You, Thank You.

 

[Jonmo1]

You're welcome