For loop in a for loop

[cicconmr]

From my knowledge of other programming languages this should seem simple enough....but of course I can't make it work...

My goal is to take one array of data, store the value then see if that value is in another array by looping through that array.

If it finds it, it simply adds 1 to a specific column. I define all the variables except cell and cellf because I'm sure not 100% sure how these for loops work?

    For Each Cell In Worksheets(1).Range("E2:E40")
        mcTicker = Cell.Value
        For Each CellF In Worksheets(3).Range("B3:B503")
            mcTicker2 = CellF.Value
            If mcTicker = mcTicker2 Then
                Range("M" & Cell.Row) = Range("M" & Cell.Row).Value + 1
            End If
        Next
    Next

Any ideas?

 

[cjcobra]

I would simplify your FOR loops so it's easy to step through it with OFFSET


For x = 1 to 8
mcTicker = Cell.Value
For y = 1 to 503
mcTicker2 = CellF.Value
If mcTicker = mcTicker2 Then
ActiveCell.Offset(0, 12).Select
mcTicker3 = Cellx.Value
Cellx = Cellx + 1
ActiveCell.Value = Cellx
ActiveCell.Offset(0, -12).Select
Else
End If
Next
Next

 

[ukmikeb]

Hi

cicconmr - You're doing fine. You define Cell and CellF as follows -

Dim Cell as Range
Dim CellF as Range

The loops work by progressing through each cell in each specified range.
For Cell it goes E2, E3, E4 ....E40 and
For CellF it goes B3, B4, B5 ...B503.

I would say for good practice that you need to qualify the Next statements with CellF and Cell in that order.

cjcobra - There is a flaw in your solution.
What Sheet and what Column is the Active Cell on?
You don't need to Select - the OP hasn't!
Where do Cellx and mcTicker3 come from?

hth

 

[ukmikeb]

Hi

Further to my previous post you need to change as follows -

Also you need to qualify the range where you add to the cell value eg -

 Worksheets(1).Range("M" & Cell.Row) = Worksheets(1).Range("M" & Cell.Row).Value + 1

change to "Worksheets(3)." if I have assumed incorrectly.

hth