Counting instances of an offset value???

Hi all,

I'm trying to count all the instances of the letter "M" if they appear 2 cells above a cell in range that has ColorIndex 3 and display the results in cell AG2 once i have this one right i will be able to adapt it to count for all the other letters in different ranges but right now i cant get this to work or do anything, any sugestions????

Sub maf()
Dim rng As Range
With ThisWorkbook.ActiveSheet
Range("AG2") = mCount
Set rng = Range( _
"B3:AF4,B7:AF8,B11:AF12,B15:AF16,B19:AF20,B23:AF24,B27:AF28,B31:AF32,B35:AF36,B39:AF40,B43:AF44,B47:AF48")
For Each mycell In rng
If mycell.Interior.ColorIndex = 3 And mycell.Offset(0, -2).Text = "m" Then
mCount = 1 + 1

End If
Next
End With

End Sub

iam not sure but try this..

change "For Each mycell In rng"

into "For Each mycell In rng.cells"

Hello Simon,

If you want to move 2 cells up, your code should be MyCell.Offset(-2, 0). Your code is moving 2 cells to the left.

Sincerely,
Lieth Ross

thanks for the replies.......my offset is (-2, 0) it was a typo on my part, but it still doesnt work.......any other suggestions????


Simon

change your mcount = 1+1

to

mcount = mcount + 1

Mangesh

Thanks Mangesh,

I tried that, th macro runs but appears to do nothing, it does not display a result in AG2, do i need a function to count things the way i want?
i.e if the color of the cell is red and the cell 2 cells above has text m then add it to the count.

Can you help?

Simon

Hi Simon,

the code you provided worked well with me. Can you provide your complete code again.

Mangesh

Another thing I noticed in your code was that you are not priniting the count at the end of the code.

Your line:
Range("AG2") = mCount
should appear just before end sub


' your code here
Range("AG2") = mCount
End Sub

This will put the count in the cell AG2

Mangesh

Cheers Mangesh!.........worked a treat!

Thanks,

Simon

Thanks for your feedback.

Mangesh