Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting instances in string | Excel Discussion (Misc queries) | |||
Counting instances of text??? | Excel Worksheet Functions | |||
Counting Multiple Instances | Excel Worksheet Functions | |||
Counting instances in a cell | Excel Discussion (Misc queries) | |||
Counting Instances of a Value? | Excel Programming |