Help with search terms, need VLOOKUP equivalent for multiple rows w/ same identifier

[a029223]

OK, I'm getting so close I can taste it, thanks to you, duane.

Now I have a workbook with 4 sheets in it --

-- First, my formatted report for one single store.
-- Then, the 3 data sheets -- storelist, storedata, and summary.

When I run the macro, the result is a list on "summary" that has AL
stores in order, but ONLY the first 20 customers for each store. Tha
can, in effect, be my new database (i.e., with all the unwante
customers eliminated).

My last step is to have the 20 customers for store 101 be brought int
the formatted report page, when I type in the store number on th
formatted report page. So:

The store # will always be input into $A$1 on the formatted report.
The top 20 customers need to display (20 rows, 6 columns of data
starting at $A$25 of the report.

In English, I want to tell the computer, "See the store number in $A$1
Well, you should go over to the summary sheet and go through the firs
column until you see the first instance of that store number, and the
grab a range of cells that's 20 rows high and 6 columns wide, copy it
bring it back over here, and paste special values."

Any thoughts?

I was imagining that the the first destination row would be a VLOOKU
but maybe it's another vba bit

 

[duane]

add section at end - I have not tested this yet.

Sub Macro1()
Sheets("summary").Range("a10:g1000").ClearContents
Dim destrange As Range
Dim i As Integer
Application.ScreenUpdating = False
i = 1
'
'stores are listed in named range "storelist"
'
storelist = Range("storelist").Value
For Each store In storelist
Set rng = Sheets("storedata").Range("a1:a1000").Find(store)
If rng Is Nothing Then GoTo skip
storerow = rng.Row
Sheets("storedata").Select
Range(Cells(storerow, 1), Cells(storerow + 19, 6)).Select
Selection.Copy
Sheets("summary").Select
'
'begin pasting store data in row 10 on summary sheet
'
Cells(20 * i - 10, 1).Select
Selection.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
i = i + 1
skip:
Next
'
' the following is added - same logic as prior
'
store = sheets("formatted report").cells(1,1).value
Set rng = Sheets("summary").Range("a1:a1000").Find(store)
If rng Is Nothing Then GoTo skip1
storerow = rng.Row
Sheets("summary").Select
Range(Cells(storerow, 1), Cells(storerow + 19, 6)).Select
Selection.Copy
Sheets("formatted report").Select
Cells(25,1).Select
Selection.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
goto skip2
skip1:
msgbox("This store is not in database")
skip2:
'
' new section ended
'
Application.ScreenUpdating = True
Range("a1").Select
End Su