macro search

[guideme]

Hi,
I have a group of account numbers that I need to search for everytime I
open a certain worksheet. I would like it to automatically search for
the account numbers listed then highlight it in yellow to alert me.

I hope somebody can help me, bec. it's a pain doing the ctrl F
then hitting backspace to erase it then entering the next account
number.

thanks!
guideme

 

[Norman Jones]

Hi Guideme,

Try:

Sub Tester()

Dim AccNo As String
Dim RngAcc As Range
Dim firstAddress As String
Dim c As Range
Dim Arr As Variant
Dim i As Long

Arr = Array("A1001", "A1005", "A1010") '<==== CHANGE

Set RngAcc = ThisWorkbook. _
Sheets("Sheet1").Range("A1:A200") '<==== CHANGE

Application.ScreenUpdating = False

'Clear pre-existing highlights
RngAcc.Interior.ColorIndex = xlNone

For i = LBound(Arr) To UBound(Arr)
AccNo = Arr(i)

With RngAcc
Set c = .Find(AccNo, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 36
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address _
<> firstAddress
End If
End With
Next i

Application.ScreenUpdating = False

End Sub

Change the Arr value to accord with the required account numbers.
Change the RngAcc address to match the full address of the range holding
account numbers. I have assumed a single column range as this seemed to me
likely to correspond with your situation; the RngAcc can, however, be set
to any range (or ranges) that you wish.

If the account numbers to be found are always the same, the array approach
adopted here would be very suitable. If, however, these are subject to
frequent change, the macro could readily be adapted to read the required
account numbers from a worksheet range. Alternatively, an inputbox could be
popped up.

In any event, try this routine on a copy of your workbook until you are
happy with the results.

 

[Norman Jones]

Hi Guideme,

Typo warning!

Change the penultimate line of the macro from:

Application.ScreenUpdating = False

to

Application.ScreenUpdating = True

 

[guideme]

THANKS! i'll try it.