Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
search macro with array
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
search macro with array
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." wrote: 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
search macro with array
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... |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
search macro with array
You could upload a workbook with sample date in to
www.savefile.com and post the link Mike "G.R." wrote: 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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please help on array search & result | Excel Worksheet Functions | |||
search an array for possibilities | Excel Worksheet Functions | |||
search array | Excel Worksheet Functions | |||
Defining an array to search by | Excel Discussion (Misc queries) | |||
Text Search in an Array | Excel Discussion (Misc queries) |