Excel increment based on value in other cell 1

[scriptscribe]

Hi,

I want to increment the value of a cell (basically a counter) each time the value "DJJ:" appears in a neighboring column. My data looks like this:

COL1 COL2
DJJ: SMITH,JOHN
PMF: SMITH,JOHN A
DJJ: JONES,DAVEY
PMF: JONES,DAVY
PMF: JONES,DAVEY K
DJJ: DOE,JOHN
PMF: DOE,JOHN L

Basically, I'm looking for something like this:

x = 1
Do
   If ActiveCell.value(in column B) <> "DJJ:"  
   Then Next Line
   Else write x
   x = x+1
While last cell in column B is not null

Thanks!
Mike

 

[SkipVought]

This can be done with the COUNTIF, if I understand your question.
[tt]
=COUNTIF(A:A,"DJJ")
[/tt]


Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[NWBeaver]

If you insist on doing it with VBA code, it might look something like this:

Sub Find_DJJ()

'Set the range of cells to look in.
Dim objRange As Range
Set objRange = Sheet1.Range("A1", Range("A65536").End(xlUp))

'Search for decimal points in numeric values.
strFind = "DJJ:"
Count = 0
For Each objCell In objRange
    If objCell.Value = strFind Then
       Count = Count + 1
    End If
Next

'Write the count value to a cell.
Range("c1").Value = Count & " Occurances of: " & strFind & " Found"

End Sub

 

[NWBeaver]

As you were... that should read like this.

Sub Find_DJJ()

'Set the range of cells to look in.
Dim objRange As Range
Set objRange = Sheet1.Range("A1", Range("A65536").End(xlUp))

'Search for occurances of a string.
strFind = "DJJ:"
Count = 0
For Each objCell In objRange
    If objCell.Value = strFind Then
       Count = Count + 1
    End If
Next

'Write the count value to a cell.
Range("c1").Value = Count & " Occurances of: " & strFind & " Found"

End Sub

Skip's suggestion would certainly be simpler thouggh.

 

[scriptscribe]

Thanks for the suggestions, guys. I guess I wasn't clear enough in my question. Here are the results I'm looking for:

COL1 COL2
1 DJJ: SMITH,JOHN
PMF: SMITH,JOHN A
2 DJJ: JONES,DAVEY
PMF: JONES,DAVY
PMF: JONES,DAVEY K
3 DJJ: DOE,JOHN
PMF: DOE,JOHN L

So essentially I need an auto-number column that is associated with each instance of "DJJ". Each name will have only one row with "DJJ", but can have as many as five with "PMF".

Thanks again!

 

[DougAJ4]

This function should do what you want:

Function SeqNum(SearchString As String, DRange As Variant) As Variant
Dim CountA() As Variant, NumRows As Long, i As Long, SCount As Long

DRange = DRange.Value
NumRows = UBound(DRange)
ReDim CountA(1 To NumRows, 1 To 1)

For i = 1 To NumRows
If DRange(i, 1) = SearchString Then
SCount = SCount + 1
CountA(i, 1) = SCount
Else
 CountA(i, 1) = ""
End If
Next i

SeqNum = CountA
End Function

Enter as an array function; i.e.
Enter the function in the top cell
Press enter
Select the whole column (down to the bottom of your list)
Press F2
Press ctrl-shift-enter

The sequence numbers should appear

HTH

Doug

 

[SkipVought]

I guess that you STILL are not clear enough
[tt]
COL1 COL2
1 DJJ: SMITH,JOHN
[/tt]

I see THREE COLUMNS.

Assuming that there are...
[tt]
A1: =COUNTIF($B$1:B1,"DJJ")
[/tt]

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]