Search cells

[davehill01]

I need a macro to search a spreadsheet for a list of numbers.

Sinario

Every day I am sent a speadsheet of a list of part numbers (in text format) and descriptions. The first thing I have to do is check this list for certain part numbers that need updating. The list is small (Max 10 numbers) and only changes at most once a month, and I should think needs to be embedded in the macro.

I cannot use conditional formatting or VLOOKUP, because that would mean doing it every day.

Can anyone offer some help

Thanks

Dave

 

[Darren Bartrup]

Welcome to the board!

This link may help:
http://www.mrexcel.com/forum/showthread.php?641583-Searching-sheets&p=3187438#post3187438

Answered almost the same query yesterday.

You may need to change this line in the code though:

Set rAddress = wrkSht.Cells.Find(What:=sSearchValue, _
                LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext)

Change xlWhole to xlPart

 

[davehill01]

Thanks Darren

As soon as I get a chance to try it I let you know how I get on.

Dave

 

[davehill01]

Darren

It didn't work for me.

I think it is too complicated, cutting and pasting info to other places.

All I need is a search and highlight. But I need the search criteria in the macro.

Dave

 

[Darren Bartrup]

Try this code, it'll highlight each find.

Sub Test()

    Dim rAddress As Range
    Dim wrkSht As Worksheet
    Dim x As Long
    Dim sFirstAddress As String
    
    '//Add search values here - change '0 To 2' to number of values to search for.
    Dim sSearchValue(0 To 2) As Variant
    
    sSearchValue(0) = "5468"
    sSearchValue(1) = "abcd"
    sSearchValue(2) = "QWWE"
    
    
    For Each wrkSht In Worksheets
        For x = LBound(sSearchValue) To UBound(sSearchValue)
            Set rAddress = Nothing
            Set rAddress = wrkSht.Cells.Find(What:=sSearchValue(x), _
                LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlNext)
            If Not rAddress Is Nothing Then
                sFirstAddress = rAddress.Address
                Do
                    With rAddress
                        .Interior.Color = 255
                        .Font.Color = -16711681
                        .Font.Bold = True
                    End With
                    Set rAddress = wrkSht.Cells.FindNext(rAddress)
                Loop While Not rAddress Is Nothing And rAddress.Address <> sFirstAddress
            End If
        Next x
    Next wrkSht

End Sub

You may want to record a macro to change the highlighting:

                    With rAddress
                        .Interior.Color = 255
                        .Font.Color = -16711681
                        .Font.Bold = True
                    End With

Currently it's red background and bold yellow text.

 

[davehill01]

Thanks Darren

That's absolutely brilliant, Works a treat.

Thanks again

Dave

 

[Darren Bartrup]

Glad it worked, and thanks for the feedback.