VBA find is most efficient than the worksheet function VLOOKUP

.Range("B2").FormulaR1C1 = _
"=VLOOKUP(RC[-1],'Store Database '!R2C1:R3000C4,2,FALSE)"

can be replaced with the following

.Range("B2").Formula = _
"=VLOOKUP(A2,'Store Database '!B2:D3000,2,FALSE)"

If the number of rows change in the Store database
SDLastRow = sheets("Store Database").Range("A" & rows.count).end(xlup).row

.Range("B2").Formula = _
"=VLOOKUP(A2,'Store Database '!B2:D" & SDLastRow & ",2,FALSE)"


" wrote:

Thanks Joel,

I learned a few things getting some of it on my own but after going
through your code, I learned alot more. I liked how you did the
..Range("B2").FormulaR1C1 =
instead of how I did. You way is much cleaner. Then the Lastrow part
seems so obvious but I had no clue it was that simple.

I did get rid of the space after Database but everything worked
perfectly!

Thanks again Joel!!



On Sat, 1 Mar 2008 18:54:01 -0800, Joel
wrote:

I modified you version

Sub get_address2()

With Sheets("Store Number")

.Range("B2").FormulaR1C1 = _
"=VLOOKUP(RC[-1],'Store Database '!R2C1:R3000C4,2,FALSE)"
.Range("C2").FormulaR1C1 = _
"=VLOOKUP(RC[-2],'Store Database '!R2C1:R3000C4,3,FALSE)"
.Range("D2").FormulaR1C1 = _
"=VLOOKUP(RC[-3],'Store Database '!R2C1:R3000C4,4,FALSE)"
Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("B2:D2").Copy _
Destination:=Range("B3:D" & Lastrow)
End With
End Sub

"Joel" wrote:

This is a very simle request. I copied the entire row from the stroe
database to store number worksheet. I assumed the store number was in colmn
A in both worksheets.


Sub get_address()


With Sheets("Store Number")
RowCount = 1
Do While .Range("A" & RowCount) < ""
StoreNum = .Range("A" & RowCount)
With Sheets("Store Database")
Set c = .Columns("A:A").Find(what:=StoreNum, _
LookIn:=xlValues, lookat:=xlWhole)
End With

If Not c Is Nothing Then
c.EntireRow.Copy _
Destination:=.Rows(RowCount)
End If
RowCount = RowCount + 1
Loop
End With
End Sub

" wrote:

Have one workbook with two worksheets (Store Number and Store
Database)

Store Database has these headers
Store Address City Zip Phone Number

There are 3000 different stores with this information on this
worksheet


Store Number currently has the identical headers.
Store Address City Zip Phone Number

Here is the difference though and our challenge.

We get a list of stores each week that we need to do a match from the
Store Database sheet. So if Store number worksheet had 23 in A1 it
would retrieve the address, city,zip and phone number from the Store
Database sheet that has the same store number. This would happen to
various amounts of stores. Meaning sometimes our Store Number
worksheet would contain 1000 stores, sometimes more , sometimes less.

The store number list is supplied and we just copy it to the Store
Number worksheet.

Thoughts, Ideas, help <grin

Thanks