+ Reply to Thread
Results 1 to 8 of 8

VLOOKUP from another sheet, VBA

  1. #1
    Guest

    VLOOKUP from another sheet, VBA

    hi,

    I have 2 sheets in my workbook,

    sheet 1 contains the main analysis of my data

    sheet 2 contains a table which I will reference with VLOOKUP from sheet 1


    I have written the following VBA code for my purpose:
    Worksheets(1).Activate //activate sheet 1
    Range("M2").FormulaR1C1 = "=VLOOKUP(RC[-5], lookuptable'!$A2:$B38, 2,
    false)" //input into cell M2 of sheet 1 this formula.

    when I run my code,
    cell M2 in sheet 1 will give a #NAME? error and when I look at the formula
    in the cell:
    =VLOOKUP(H2,personal.xls!'A2':'B38',2,FALSE)

    notice the extra single quotes around A2 and B38.

    ps: i defined my module in personal.xls to be able to access it from any
    workbook.

    thnks.
    Michael.


  2. #2
    Gordon
    Guest

    Re: VLOOKUP from another sheet, VBA

    <a> wrote in message news:[email protected]...
    > hi,
    >
    > I have 2 sheets in my workbook,
    >
    > sheet 1 contains the main analysis of my data
    >
    > sheet 2 contains a table which I will reference with VLOOKUP from sheet 1
    >
    >
    > I have written the following VBA code


    I might be missing something here, but why VBA code? Won't just an ordinary
    VLOOKUP do what you want, especially as it's looking up a sheet in the same
    workbook?



  3. #3
    Guest

    Re: VLOOKUP from another sheet, VBA

    I am trying to automate the whole process so that
    at the press of a button, the data processing and analysis can be done .

    yes, it works if it is just a normal formula in a cell.
    but in VBA, i have problems referencing it .


    "Gordon" <[email protected]> wrote in message
    news:[email protected]...
    > <a> wrote in message news:[email protected]...
    >> hi,
    >>
    >> I have 2 sheets in my workbook,
    >>
    >> sheet 1 contains the main analysis of my data
    >>
    >> sheet 2 contains a table which I will reference with VLOOKUP from sheet 1
    >>
    >>
    >> I have written the following VBA code

    >
    > I might be missing something here, but why VBA code? Won't just an
    > ordinary VLOOKUP do what you want, especially as it's looking up a sheet
    > in the same workbook?
    >



  4. #4
    KL
    Guest

    Re: VLOOKUP from another sheet, VBA

    Hi,

    I guess it is clearly beacuse you are mixing the R1C1 notation (RC[-5]) with A1 notation ($A2:$B38) in one formula. Also you use only one '-sign whereas it should be on both sides of the sheet name (actually with this name they aren't necessary at all as there are no spaces in it). Besides you are explicitly saying it is a R1C1 formula by "Range("M2").FormulaR1C1=". Try this:

    Range("M2").FormulaR1C1="=VLOOKUP(RC[-5], lookuptable!R[-3]C1:R[33]C2, 2,0)"

    Regards,
    KL



    <a> wrote in message news:[email protected]...
    > hi,
    >
    > I have 2 sheets in my workbook,
    >
    > sheet 1 contains the main analysis of my data
    >
    > sheet 2 contains a table which I will reference with VLOOKUP from sheet 1
    >
    >
    > I have written the following VBA code for my purpose:
    > Worksheets(1).Activate //activate sheet 1
    > Range("M2").FormulaR1C1 = "=VLOOKUP(RC[-5], lookuptable'!$A2:$B38, 2,
    > false)" //input into cell M2 of sheet 1 this formula.
    >
    > when I run my code,
    > cell M2 in sheet 1 will give a #NAME? error and when I look at the formula
    > in the cell:
    > =VLOOKUP(H2,personal.xls!'A2':'B38',2,FALSE)
    >
    > notice the extra single quotes around A2 and B38.
    >
    > ps: i defined my module in personal.xls to be able to access it from any
    > workbook.
    >
    > thnks.
    > Michael.
    >


  5. #5
    Gordon
    Guest

    Re: VLOOKUP from another sheet, VBA

    <a> wrote in message news:[email protected]...
    >I am trying to automate the whole process so that
    > at the press of a button, the data processing and analysis can be done .


    But you are referencing a sheet in the same workbook, so when you open the
    workbook, the VLOOKUP will automatically update! So I ask again, am I
    missing something? Why the VBA?



  6. #6
    KL
    Guest

    Re: VLOOKUP from another sheet, VBA

    ....actually, I would do this:

    Range("M2").FormulaR1C1 = "=VLOOKUP(RC[-5], lookuptable!R2C1:R38C2, 2,0)"

    Regards,
    KL


    "KL" <[email protected]> wrote in message news:[email protected]...
    Hi,

    I guess it is clearly beacuse you are mixing the R1C1 notation (RC[-5]) with A1 notation ($A2:$B38) in one formula. Also you use only one '-sign whereas it should be on both sides of the sheet name (actually with this name they aren't necessary at all as there are no spaces in it). Besides you are explicitly saying it is a R1C1 formula by "Range("M2").FormulaR1C1=". Try this:

    Range("M2").FormulaR1C1="=VLOOKUP(RC[-5], lookuptable!R[-3]C1:R[33]C2, 2,0)"

    Regards,
    KL



    <a> wrote in message news:[email protected]...
    > hi,
    >
    > I have 2 sheets in my workbook,
    >
    > sheet 1 contains the main analysis of my data
    >
    > sheet 2 contains a table which I will reference with VLOOKUP from sheet 1
    >
    >
    > I have written the following VBA code for my purpose:
    > Worksheets(1).Activate //activate sheet 1
    > Range("M2").FormulaR1C1 = "=VLOOKUP(RC[-5], lookuptable'!$A2:$B38, 2,
    > false)" //input into cell M2 of sheet 1 this formula.
    >
    > when I run my code,
    > cell M2 in sheet 1 will give a #NAME? error and when I look at the formula
    > in the cell:
    > =VLOOKUP(H2,personal.xls!'A2':'B38',2,FALSE)
    >
    > notice the extra single quotes around A2 and B38.
    >
    > ps: i defined my module in personal.xls to be able to access it from any
    > workbook.
    >
    > thnks.
    > Michael.
    >


  7. #7
    Guest

    Re: VLOOKUP from another sheet, VBA

    thanks alot.

    since i am at cell M2 on sheet 1,
    the code : lookuptable!R[-3]C1:R[33]C2 is taking pivot from which cell?
    should i take reference from A1 since this is on sheet 2.

    also, i am going to copy this whole formula down the column, will R1C1
    formula automatically shift the referencing for me?
    i.e. if M2 calculates from cell H2, M3 will calculate from cell H3.

    thanks again.

    "KL" <[email protected]> wrote in message
    news:[email protected]...
    Hi,

    I guess it is clearly beacuse you are mixing the R1C1 notation (RC[-5])
    with A1 notation ($A2:$B38) in one formula. Also you use only one '-sign
    whereas it should be on both sides of the sheet name (actually with this
    name they aren't necessary at all as there are no spaces in it). Besides you
    are explicitly saying it is a R1C1 formula by "Range("M2").FormulaR1C1=".
    Try this:

    Range("M2").FormulaR1C1="=VLOOKUP(RC[-5], lookuptable!R[-3]C1:R[33]C2,
    2,0)"

    Regards,
    KL



    <a> wrote in message news:[email protected]...
    > hi,
    >
    > I have 2 sheets in my workbook,
    >
    > sheet 1 contains the main analysis of my data
    >
    > sheet 2 contains a table which I will reference with VLOOKUP from sheet

    1
    >
    >
    > I have written the following VBA code for my purpose:
    > Worksheets(1).Activate //activate sheet 1
    > Range("M2").FormulaR1C1 = "=VLOOKUP(RC[-5], lookuptable'!$A2:$B38, 2,
    > false)" //input into cell M2 of sheet 1 this formula.
    >
    > when I run my code,
    > cell M2 in sheet 1 will give a #NAME? error and when I look at the

    formula
    > in the cell:
    > =VLOOKUP(H2,personal.xls!'A2':'B38',2,FALSE)
    >
    > notice the extra single quotes around A2 and B38.
    >
    > ps: i defined my module in personal.xls to be able to access it from any
    > workbook.
    >
    > thnks.
    > Michael.
    >



  8. #8
    KL
    Guest

    Re: VLOOKUP from another sheet, VBA

    Hi,

    >since i am at cell M2 on sheet 1,
    >the code : lookuptable!R[-3]C1:R[33]C2 is taking pivot from which cell? should i take reference from A1 since this is on sheet 2.


    I am not sure what you mean by this :-(
    The reference to table should be lookuptable!R2C1:R38C2 if you want $A$1:$B$38

    >also, i am going to copy this whole formula down the column, will R1C1 formula automatically shift the referencing for me?
    >i.e. if M2 calculates from cell H2, M3 will calculate from cell H3.


    Yes, it will.

    Regards,
    KL

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1