Complicated Macro Help!!!

S

Sath

New Member
Joined
Aug 18, 2011
Messages
5
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.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
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...
 
Upvote 0
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.

Code: 
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
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Always open xlsx files in the already-open instance of Excel
Replies
2
Views
452
nevyoung
N
R
  • Solved
Character count on each line within one cell
3 4 5
Replies
40
Views
767
Peter_SSs
Peter_SSs
C
  • Solved
Extracting data with VBA macro button from files in designated folder
2 3
Replies
20
Views
1K
Micron
M
B
  • Question Question
Macro to look text in the right column and set a variable character count
Replies
2
Views
122
Peter_SSs
Peter_SSs
edge37
  • Solved
Inserting more than one comment in a cell
Replies
4
Views
395
edge37
edge37

Forum statistics

Threads
1,224,527
Messages
6,179,337
Members
452,907
Latest member
Roland Deschain

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top