Refer to cell using for each

[Lazhal]

Hi I have this code

Dim Count As Integer
For Each c1 In Worksheets("Sheet2").Range("A11:A24").Cells
    Count = 0
    For Each c2 In Worksheets("Sheet1").Range("C1:C200").Cells
        If (c1.Value = c2.Value) Then
            Count = Count + 1
        End If
    Next
    c1.Value = Count
Next

But what I want is a change in the last line

c1.Value = Count

to something like

c1+NextRowOver.Value = Count

Essentially, I want to use the cell in the next row over from c1. How can I do this?

Thank you.

[JeanRage]

Hi,

c1.Offset(1,0).Value = Count

HTH

[DonkeyOte]

I think by "next row over" you mean next column - ie B11:B24

It would be a lot quicker to replace your entire code with something like:

With Worksheets("Sheet2").Range("A11:A24").Offset(,1)
    .FormulaR1C1 = "=COUNTIF(Sheet1!R1C3:R200C3,RC[-1])"
    .Value = .Value
End With

always better to work with cells in blocks than iterate.

[StephenR]

c1.Offset(, 1).Value = Count

But why not just use COUNTIF formula?

[Lazhal]

great!

thanks folks!