Counting instances of an offset value???

  • Thread starter Thread starter Simon Lloyd
  • Start date Start date
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
 
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 Ros
 
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
 
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
 
You must log in or register to reply here.
Back
Top