Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Vlookup via VBA

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

    "Interfere? Of course we should interfere! Always do what you're best at, that's what I say!" -- The Doctor, Nightmare of Eden

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •