For Each loop not working

ebraatz

Board Regular
Joined
Feb 25, 2017
Messages
76
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?

Code:
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
 
Last edited:

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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?
 
Upvote 0
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
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

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
Back
Top