Conducting search through a list help (same search again, but with different variable selected from list)

[jjsauer]

Hello,

I have a macro that I need to rewrite because the data file changes. The new macro needs to be based on other cues (unlike before is was spacing and offsetting -->Im a rookie).

I need to search for three things, then return a value from a different column. Where I am running into trouble automating it, is when I try to make it work with a list. The 1st and 3rd part of the search are consistent, but not unique. The 2nd is unique. All three are in the same column. I tried a For Each rCell type thing, but it just skipped that part...Im a little lost how to cycle. Thanks in advance.

Also, while your looking, if you know of a way to consolidate the 3 searches that would be great. Like a Search for A, then search for B after A, then search for C after B.


Sub Search()
Dim rFoundCell As Range
Dim rCell As Range


Windows("FY12-Q3 Value Tracker Key Measure Summary Tables FINAL.xlsm").Activate
Sheets("Store Level Value").Activate
For Each rCell In Range("B4:B10")
Windows("FY12-Q3 Data Tables.xlsx").Activate
Sheets("Total").Select
With Columns(1)
Set c = .Find("[Q6]", LookIn:=xlValues)
c.Select
Set rFoundCell = ActiveCell
'here is my problem
Set c = .Find(rCell, After:=rFoundCell, LookIn:=xlValues)
c.Select
Set rFoundCell = ActiveCell
Set c = .Find("Bottom 2 (NET)", After:=rFoundCell, LookIn:=xlValues)
c.Select
End With
Selection.Offset(0, 14).Copy
Windows("FY12-Q3 Value Tracker Key Measure Summary Tables FINAL.xlsm").Activate
Sheets("Store Level Value").Activate
rCell.Select
Selection.Offset(0, 11).Select
Selection.PasteSpecial Paste:=xlPasteValues
Next rCell
End Sub

 

[wigi]

In that red line, what are you looking for?

 

[wigi]

Consider more concise code like this (which will still show the issue but shows how to improve on your code):

Sub Search()
    
    Dim rCell As Range

    For Each rCell In Windows("FY12-Q3 Value Tracker Key Measure Summary Tables FINAL.xlsm").Sheets("Store Level Value").Range("B4:B10").Cells
        With Workbooks("FY12-Q3 Data Tables.xlsx").Sheets("Total").Columns(1)
            Set c = .Find("[Q6]", , LookIn:=xlValues)
            Set c = .Find(rCell, After:=c)
            Set c = .Find("Bottom 2 (NET)", After:=c)
        End With
        rCell.Offset(0, 11).Value = c.Offset(, 14).Value
    Next
    
End Sub

 

[jjsauer]

Wigi, thanks for the consolidation, will be most helpful.

What I am trying to do is run the same search, say 7-8 times in a row, with one variable being rotated in and out via a list on another spreadsheet. I only inserted rCell because I was already using it to only run the search the same number of times as the list.

Make sense? I was hoping to find a way from copying and pasting that same code 7-8 times and changing one of the search variables for each. The code would be a monster at that point.

Thanks!

 

[wigi]

You can use rCell to make a loop through a range of cells. Actually, the code I suggested already does this!
(because that's the way I interpreted what you wanted to achieve)
If wanted, you could explicitly use rCell.Value or rCell.Value2 or rCell.Text to refer to the contents of rCell (in the loop over cells B4:B10 in the respective worksheet).

 

[jjsauer]

Wigi, I cannot thank you enough. I will test it out right now.

Thanks!