Complicated Macro Help!!!

[Sath]

Hey,

I need help finding a macro that can select a number of cells that have a certain text within them (xxxxx) and count how many there are. Now, here is the trickiest part, is there any way to ONLY select cells with this text (xxxxx) that appear before another cell with different text (yyyyy) in the same collumn.

Thanks in advance for your help.

 

[Engineer Joe]

Sath,

If you post and no one responds, then just reply to yourself in the forum with "Bump" and your message will go back up to the top so that anyone checking for new posts will see your inquiry.

Now, for your question. Will "xxxx" be in the cell directly above "yyyy"? Also, are you looking for an exact match, or partial match? Is there only one instance of "yyyy" and you want to count all the "xxxx"s above it, or you want to count every "xxxx" with a "yyyy" below it? Do you actually want to select those cells, or do you want to just know the numbers of them or visually identify them (e.g. color them)? Any of these can be done, but selecting them means you probably want to do something with them directly afterwards...

 

[Engineer Joe]

here's a start. depending on the answers to my questions, this may need tweaking. Also, if "xxxx" and "yyyy" ever change, you may want to have a user input box for editing them, which isn't hard, and I can add it.

Sub getmyrange()
Dim multirange As Range
Dim cll As Range
Dim fndcells As Long

fndcells = 0
For Each cll In Selection.Cells
    If cll.Value = "xxxx" Then
        If cll.Offset(1, 0).Value = "yyyy" Then
            fndcells = fndcells + 1
            If multirange Is Nothing Then
                Set multirange = cll
            Else
                Set multirange = Union(multirange, cll)
            End If
            
        End If
    End If
    
Next cll

MsgBox ("I found " & fndcells & " cells that match your criteria.")
If Not multirange Is Nothing Then
    
    multirange.Select
    
End If


End Sub

 

[Sath]

Hey Joe, thanks for your reply. I really appreciate your help.

I am looking to count every "xxxx" above a "yyyy" cell within the same collumn. There is more than one instance. Furthermore I am seeking an exact match and I am more concerned with the number of these cells. These cells have only text and have no values.