Results 1 to 5 of 5

Using a Lookup table in VBA

This is a discussion on Using a Lookup table in VBA within the Excel Questions forums, part of the Question Forums category; Hi, I need to pull data from one workbook into another based on a lookup table. one workbook contains the ...

  1. #1
    New Member
    Join Date
    Jun 2012
    Posts
    38

    Default Using a Lookup table in VBA

    Hi,

    I need to pull data from one workbook into another based on a lookup table.

    one workbook contains the lookup information. This is a projections workbook.- I need to pull data over to another workbook based on the report that is run. The report is created using VBA, so need to lookup the data in Column A of the Report Book and use the information from the projections book accordingly.

    Any suggestions.

    Thanks,
    Scott

  2. #2
    Board Regular
    Join Date
    Sep 2012
    Location
    Melbourne Australia
    Posts
    1,386

    Default Re: Using a Lookup table in VBA

    to use vlookup in VBA, you need to use:
    Application.WorksheetFunction.CountIf

    The structure is the same as in Excel. Vlookup(Lookup value,table,column,true/false)

    Hope that helps with your development.

  3. #3
    New Member
    Join Date
    Jun 2012
    Posts
    38

    Default Re: Using a Lookup table in VBA

    Quote Originally Posted by dermie_72 View Post
    to use vlookup in VBA, you need to use:
    Application.WorksheetFunction.CountIf

    The structure is the same as in Excel. Vlookup(Lookup value,table,column,true/false)

    Hope that helps with your development.
    Know this is going to make me sound lost but kind of am on this.

    Tried this Code- and it did not work;

    Application.WorksheetFunction.CountIf(RC[-6],'[5100 Projections.xlsm]Database'!R1C1:R728C7,6)

    Any suggestions?

    Thanks,

  4. #4
    Board Regular
    Join Date
    Sep 2012
    Location
    Melbourne Australia
    Posts
    1,386

    Default Re: Using a Lookup table in VBA

    have you tried using the countif function in excel, as in typing the formula out in a cell, and seeing if it returns a value?
    Click on the fx in the formula bar if you want to play with some of the values.
    I'm guessing it's the 6 at the end, but I could be wrong. Try changing that tot a false, I believe a 1 or 0 will also work.

    Hope that helps.

  5. #5
    Board Regular
    Join Date
    Sep 2012
    Location
    Melbourne Australia
    Posts
    1,386

    Default Re: Using a Lookup table in VBA

    I think I may have it. When I gave you the first function, I put countif instead of vlookup.

    Try:

    Application.WorksheetFunction.VLookUp(RC[-6],'[5100 Projections.xlsm]Database'!R1C1:R728C7,6)
    Write a man a macro he is happy for the day....teach a man to write a macro, he'll be happy forever!

Tags for this Thread

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