Vlookup via VBA

[Bob]

This is SLOW, any suggestions?

For Lookin = 2 To LR
LookRange = Sheets("Query").Range("E" & Lookin)
InRange = Sheets("Character Values").Range("A:B")
Answer1 = Application.WorksheetFunction.VLookup(Sheets("Query").Range("E" & Lookin), Sheets("Character Values").Range("A:B"), 2, False)

Sheets("Query").Range("G" & Lookin).Value = Answer1
Next Lookin

 

[Tim Francis-Wright]

This may speed it up a bit:

1) You use
LookRange = Sheets("Query").Range("E" & Lookin)
InRange = Sheets("Character Values").Range("A:B")

to define two variables that you never use in the definition of Answer1.
Try getting rid of them. At least move the definition of InRange outside the loop.

2) If you defined LookRange (before the loop) as
Set LookRange = Sheets("Query").Range(Cells(2,5),Cells(LR,5))

and

Set InRange = = Sheets("Character Values").Range("A:B")

then you could use:

For Lookin = 2 To LR
Answer1 = Application.WorksheetFunction.VLookup(LookRange.Cells(Lookin-1),InRange,2,False)
Sheets("Query").Range("G" & Lookin).Value = Answer1
Next Lookin

HTH!