Counting instances of an offset value???

S

Simon Lloyd

Hi all,

I'm trying to count all the instances of the letter "M" if they appea
2 cells above a cell in range that has ColorIndex 3 and display th
results in cell AG2 once i have this one right i will be able to adap
it to count for all the other letters in different ranges but right no
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 Su
 
D

d61helix

iam not sure but try this..

change "For Each mycell In rng"

into "For Each mycell In rng.cells
 
L

Leith Ross

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 Ros
 
S

Simon Lloyd

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
 
S

Simon Lloyd

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
 
M

mangesh_yadav

Hi Simon,

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

Mangesh
 
M

mangesh_yadav

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
 
Top