Counting instances of an offset value???
 

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

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=37796

Counting instances of an offset value???
 

iam not sure but try this..

change "For Each mycell In rng"

into "For Each mycell In rng.cells

--
d61heli
-----------------------------------------------------------------------
d61helix's Profile: http://www.excelforum.com/member.php...fo&userid=2009
View this thread: http://www.excelforum.com/showthread.php?threadid=37796

Counting instances of an offset value???
 

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

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=37796

Counting instances of an offset value???
 

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


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=377966

Counting instances of an offset value???
 

change your mcount = 1+1

to

mcount = mcount + 1

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=377966

Counting instances of an offset value???
 

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


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=377966

Counting instances of an offset value???
 

Hi Simon,

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

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=377966

Counting instances of an offset value???
 

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


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=377966

Counting instances of an offset value???
 

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

Thanks,

Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=377966

Counting instances of an offset value???
 

:)
Thanks for your feedback.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=377966