Mark cells depending on search results.

[Will S]

Hello,

I'm trying to narrow down a list of 16,000 parts. unfortunately this list has been built up over several years and there has been no standard set as to what is in each column, I need to search through each cell and look for the letters "ABB" these are probably not the only thing in the cell so that pretty much puts me out of my area of knowledge.

Not only do I need to find these cells, I need to mark that row so I can run a different macro afterwards, either with an additional row or maybe a set colour (Anything excel and read to make it stand out from rows that don't have "ABB" somewhere in it.

I've copied a code I've found that looks to be doing something similar, I'm just curious as from what it seems, it only works for when there is just the one result as it contains no loops or anything that would make it seem to apply to multiple cells, but this could be just me misreading.

Sub Sample()    
Dim ws As Worksheet    
Dim aCell As Range   
 Dim X As Integer    
Set ws = ThisWorkbook.Sheets("Partnumber")    
With ws        Set aCell = .Columns(2).Find(What:="ABB", LookIn:=xlValues, _  
                  LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ 
                   MatchCase:=False, SearchFormat:=False)

        If Not aCell Is Nothing Then
            X = aCell.Row
            Range("I" & X).Formula = "1"
        Else
        End If
    End With
End Sub

I'm certain I'll need to change this, firstly from how I'm reading this, it is just looking at "Column 2" I'll need mine to look at all off them. Also as said above I don't think this would work for when there are more than one result, could anyone suggest a way of looping it.

As you can see from the bits of the code that make about as much sense as a bra for snakes, I've tried to adapt it myself to see if I can get it to work but I just can't seem to find a way for it to do a "Find All" function or a way of looping it.

I'm pretty much blind with this area of code so any insight would be a huge help.

Best regards,

 

[JLeno]

I think something like this should work (now it colors each row red with ABB in column B):

Sub Sample()
Dim ws As Worksheet
Dim aCell As Range
Set ws = ThisWorkbook.Sheets("Partnumber")
For Each aCell In Application.Intersect(ws.Range("B:B"), ws.UsedRange)
    
    If UCase(aCell.Value) Like "*ABB*" Then
        
        aCell.EntireRow.Interior.Color = RGB(255, 0, 0)
    
    End If
    
Next aCell
End Sub

Cheers!

 

[Will S]

Thanks for the input, I've tried it out but there seems to be an issue. When I press it to run, nothing happens. No error message, doesn't even seem to run a process (I'd assume that searching 16,000+ rows would at least make the PC stop and think for a bit) yet it doesn't really do anything. The macro is running (Tested by putting "Select A1" at the end). I can only assume that the issue is with using "Like" function, in the original page I got the code from it warned against using it for this sort of application.

I can probably pull something together from the two codes so you've still been a great help. Thanks.

 

[JLeno]

Thanks for the input, I've tried it out but there seems to be an issue. When I press it to run, nothing happens. No error message, doesn't even seem to run a process (I'd assume that searching 16,000+ rows would at least make the PC stop and think for a bit) yet it doesn't really do anything. The macro is running (Tested by putting "Select A1" at the end). I can only assume that the issue is with using "Like" function, in the original page I got the code from it warned against using it for this sort of application.

I can probably pull something together from the two codes so you've still been a great help. Thanks.

Well it worked for me, so i think it should work in your spreadsheet too.

A couple of things you should check:
1. Is the relevant data (with ABB in it) in column B? If not, amend my code.

2. Does the coloring line work? Check by selecting the line ' aCell.EntireRow.Interior.Color = RGB(255, 0, 0)' in my code and press F9. Then run the macro; if it stops on that line, the macro does find the right cells.

3. If not, find the first row that should be colored red manually and try the following:

Sub FindABB()
Dim ws As Worksheet
Dim aCell As Range
Set ws = ThisWorkbook.Sheets("Partnumber")
Set aCell = ws.Range("B2") 'change row to the first row with a hit
Debug.Print UCase(aCell.Value)
Debug.Print UCase(aCell.Value) Like "*ABB*"
End Sub

And copy and paste the output of the Immediate Window in this thread (if you don't have that window, press Ctrl+G in VBE)

 

[Will S]

Ah, that could be it as I didn't realise it was just looking at column B, I was trying to do a range of A:H (Because as there has been no set standard over the years the needed data could be in any of those columns) but that kept hitting issues. Although I think I'll just amend it back and then just run the macro again for each row.

Thanks for all the help you've been great.

 

[Will S]

Tried it with it repeating for each column at it worked great you've been loads of help and it was much faster than I thought it would be.

Tried it on a different sheet and took me a while to fully understand and dissect the differences and why it wasn't working but your tests certainly helped great and I feel I'm actually walking away understanding the code fully. Thank you for all the help it's been greatly appreciated.

 

[JLeno]

Tried it with it repeating for each column at it worked great you've been loads of help and it was much faster than I thought it would be.

Tried it on a different sheet and took me a while to fully understand and dissect the differences and why it wasn't working but your tests certainly helped great and I feel I'm actually walking away understanding the code fully. Thank you for all the help it's been greatly appreciated.

Glad to be of help