Results 1 to 5 of 5

Keeping VLOOKUP table array fixed in VBA

This is a discussion on Keeping VLOOKUP table array fixed in VBA within the Excel Questions forums, part of the Question Forums category; I am using VBA to run a VLOOKUP, but when it runs, the table array moves and therefore is not ...

  1. #1
    New Member
    Join Date
    Jul 2010
    Posts
    3

    Default Keeping VLOOKUP table array fixed in VBA

    I am using VBA to run a VLOOKUP, but when it runs, the table array moves and therefore is not returning results. Below is the syntax and I am having trouble fixing the range to

    ..ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],RC[-7]:R[1000]C[-6],2,FALSE)"..

    each time this moves down a row, the array misses entries above it in the table. Any suggestions?

    Thanks

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    151

    Default Re: Keeping VLOOKUP table array fixed in VBA

    can you specify the range (columns and rows) that you are using in the range of the vlookup ?

  3. #3
    New Member
    Join Date
    Jul 2010
    Posts
    3

    Default Re: Keeping VLOOKUP table array fixed in VBA

    This is the VLOOKUP that I am trying to get working in the macro

    =VLOOKUP(G2,$A$2:$B$1000,2,FALSE)

    Hope that gives enough info.

  4. #4
    Board Regular
    Join Date
    Apr 2010
    Posts
    151

    Default Re: Keeping VLOOKUP table array fixed in VBA

    try using this entire syntax instead of the one you specified....

    ActiveCell.Formula = "=VLOOKUP(G2,$A$2:$B$1000,2,FALSE)"

  5. #5
    New Member
    Join Date
    Jul 2010
    Posts
    3

    Default Re: Keeping VLOOKUP table array fixed in VBA

    Thanks niceguy,

    I've tried that had got an error, but you put me on the right track again.
    I got it to work using this
    "=VLOOKUP(RC[-1],R2C[-7]:R1000C[-6],2,FALSE)"

    Cheers

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
  •  


DMCA.com