search macro with array

[G.R.]

Hello. 3rd try, hope this post goes through.
Using Excel X for Mac. Need a macro to search column Q for a list of stock
ticker symbols for funds owned. When one is found, highlight that row and
shade it light green. Thought I would make my first try at an array and
cobbled together the macro below. It doesn't crash or return error messages
but it also doesn't work. Stepping through it I noticed it dumps out of the
Do loop after nine cycles and there are 9 fund names in the list (array).
Could be a clue, but to what I don't know. Any help would be appreciated.

Sub NLFI_Owned_Funds()
'
' NLFI_Owned_Funds Macro
' highlight owned funds
'
Dim OwnedFunds As Variant
Dim FoundCell As Range
Dim I As Long
Dim myRng As Range
Dim sh As Worksheet
'use the ActiveSheet
Set sh = ActiveSheet

'search tickers in column Q
Set myRng = sh.Range("Q:Q")

'define array of TICKERS FOR OWNED FUNDS
OwnedFunds = Array("PSPFX", "TRREX", "NTHEX", "BUFBX", "VASVX", _
"ACTIX", "DISVX", "FAIRX", "HIINX")

'search the values in MyRng
With myRng

For I = LBound(OwnedFunds) To UBound(OwnedFunds)
Do
Set FoundCell = myRng.Find(What:=OwnedFunds(I), _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
'possibly this should be LookIn:=xlValues
If FoundCell Is Nothing Then
Exit Do
Else
' select the entire row and shade light green
FoundCell.EntireRow.Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End If
Loop
Next I

End With
End Sub

 

[Mike H]

Hi,

Try this

Sub stantiall()
Dim myrange, myrange1 As Range
Dim OwnedFunds As Variant
Lastrow = Cells(Rows.Count, "Q").End(xlUp).Row
Set myrange = Range("Q1:Q" & Lastrow)
OwnedFunds = Array("PSPFX", "TRREX", "NTHEX", "BUFBX", "VASVX", _
"ACTIX", "DISVX", "FAIRX", "HIINX")
For Each c In myrange
For I = LBound(OwnedFunds) To UBound(OwnedFunds)
If c.Value = OwnedFunds(I) Then
If myrange1 Is Nothing Then
Set myrange1 = c.EntireRow
Else
Set myrange1 = Union(myrange1, c.EntireRow)
End If
End If
Next
Next
If Not myrange1 Is Nothing Then
myrange1.Select
End If
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End Sub

Hope this is third time lucky!!

Mike

 

[G.R.]

Mike,
Sorry to not get back to you sooner. 3 out of 5 times that I try to post or
reply, it just goes into the ether. Hopefully this one goes through.
Thanks very much for the reply -- and so quickly. Your solution of finding
all the OwnedFund rows first, adding them to a named range, and then
highlighting them all at once is much more elegant.
When I ran this, though, the only result was that the active cell when the
macro started is shaded green. Nothing else. I tried selecting column Q and
then running the macro. Ended up with column Q shaded green but nothing else.
I tried stepping through it and, from what I can tell, it is checking each
cell nine times (seems right, 9 tickers in OwnedFund array) then going on to
the next cell. I don't know how to tell if these cells are in column Q and
there are nearly a thousand rows, so i can't step through all of them to get
to the part of the macro that highlights.
Is there more info I can supply?
Thanks again for the time and attention.
Gordon
Fingers crossed the MS gods will allow this reply onto the board...

 

[Mike H]

You could upload a workbook with sample date in to
www.savefile.com
and post the link

Mike