Hi All,

I have a dilemma. I need to countif a range is like a value. I.e. I
need to count how many JUL appear in a certain range, the problem is
that the JUL may not always be in a cell on its own - it may have other
values such as JUL, AUG, SEP etc.

Is this possible to count. The code I currenty have is:

Sub countMth()
Dim i, ii As Long
Dim iii As Variant
Dim rR As Range
Dim C As Integer

C = 0

i = ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTyp eLastCell).Row
'find stuff
Cells(1, 1).Select
ii = Cells.Find(What:="Month Appeared", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=True).Row
Cells(1, 1).Select
iii = Split(Cells.Find(What:="Month Appeared", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=True).Address, "$")


For Each rR In Range(iii(1) & ii + 1 & ":" & iii(1) & i)

If myCheck = rR.Value Like "JUL" Then
C = C + 1
End If
Range("A5") = C
Next rR


End Sub


--
moglione1
------------------------------------------------------------------------
moglione1's Profile: http://www.excelforum.com/member.php...o&userid=26414
View this thread: http://www.excelforum.com/showthread...hreadid=538458

You can use wildcards in the COUNTIF worksheet function:

=COUNTIF(A1:A100,"*JUL*")

So you don't need VBA for this. However, if you need
to do it in a macro for some reason, you could use
Application.CountIf(myRange,"*JUL*")



moglione1 wrote:
Hi All,

I have a dilemma. I need to countif a range is like a value. I.e. I
need to count how many JUL appear in a certain range, the problem is
that the JUL may not always be in a cell on its own - it may have other
values such as JUL, AUG, SEP etc.

Is this possible to count. The code I currenty have is:

Sub countMth()
Dim i, ii As Long
Dim iii As Variant
Dim rR As Range
Dim C As Integer

C = 0

i = ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTyp eLastCell).Row
'find stuff
Cells(1, 1).Select
ii = Cells.Find(What:="Month Appeared", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=True).Row
Cells(1, 1).Select
iii = Split(Cells.Find(What:="Month Appeared", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=True).Address, "$")


For Each rR In Range(iii(1) & ii + 1 & ":" & iii(1) & i)

If myCheck = rR.Value Like "JUL" Then
C = C + 1
End If
Range("A5") = C
Next rR


End Sub


--
moglione1
------------------------------------------------------------------------
moglione1's Profile: http://www.excelforum.com/member.php...o&userid=26414
View this thread: http://www.excelforum.com/showthread...hreadid=538458

You can use wildcards in the COUNTIF worksheet function:

=COUNTIF(A1:A100,"*JUL*")

So you don't need VBA for this. However, if you need
to do it in a macro for some reason, you could use
Application.CountIf(myRange,"*JUL*")



moglione1 wrote:
Hi All,

I have a dilemma. I need to countif a range is like a value. I.e. I
need to count how many JUL appear in a certain range, the problem is
that the JUL may not always be in a cell on its own - it may have other
values such as JUL, AUG, SEP etc.

Is this possible to count. The code I currenty have is:

Sub countMth()
Dim i, ii As Long
Dim iii As Variant
Dim rR As Range
Dim C As Integer

C = 0

i = ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTyp eLastCell).Row
'find stuff
Cells(1, 1).Select
ii = Cells.Find(What:="Month Appeared", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=True).Row
Cells(1, 1).Select
iii = Split(Cells.Find(What:="Month Appeared", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=True).Address, "$")


For Each rR In Range(iii(1) & ii + 1 & ":" & iii(1) & i)

If myCheck = rR.Value Like "JUL" Then
C = C + 1
End If
Range("A5") = C
Next rR


End Sub


--
moglione1
------------------------------------------------------------------------
moglione1's Profile: http://www.excelforum.com/member.php...o&userid=26414
View this thread: http://www.excelforum.com/showthread...hreadid=538458