Consulting

Results 1 to 14 of 14

Thread: VLOOKUP but the cell will be different everyday

  1. 10-21-2010, 01:26 PM #1
    davmec93
    davmec93 is offline
    VBAX Regular davmec93's Avatar
    Joined
    Oct 2010
    Posts
    33
    Location

    VLOOKUP but the cell will be different everyday

    Hi All! I have a question. In my spreadsheet I am using VLOOKUP to get the qty of production from another sheet. The issue I'm having is that today the cell in my formula is in column D. Tomorrow it will be AD and so on. My question is how can I put that into my VLOOKUP? I thought about using an xlToRight and then offset a -2 but I'm not sure how to add that to the VLOOKUP. Here is what I'm currently using...it worked today but it won't tomorrow.
    [VBA]For Counter = 1 To 6
    Set curCell = Worksheets("ProjectedCompletion").Cells(Counter, 10)
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R[0]C[-9],'Summary'!R2C1:'Summary'!R580C24,24,FALSE)"
    ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select
    Next Counter[/VBA]
    What happens if you get scared half to death twice?
    Reply With Quote Reply With Quote
  2. 10-21-2010, 02:15 PM #2
    CharlesH
    CharlesH is offline
    VBAX Regular
    Joined
    Oct 2010
    Posts
    73
    Location
    hi,

    Check this thread it may help you develope a code for what you want
    http://www.vbaexpress.com/forum/showthread.php?t=34524
    It was one that I worked on with the help of Simon.
    My Time is valuable. The forum's time is valuable.
    So take time to say "Thanks"

    As with all programs there may be an easier way!
    CharlesH
    Reply With Quote Reply With Quote
  3. 10-21-2010, 02:15 PM #3
    mdmackillop
    mdmackillop is offline
    Administrator
    VP-Knowledge Base     VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Change the data to absolute references or use range names

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC4,'Summary'!R2C1:'Summary'!R580C24,24,FALSE)"
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.

    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'
    Reply With Quote Reply With Quote
  4. 10-21-2010, 02:20 PM #4
    davmec93
    davmec93 is offline
    VBAX Regular davmec93's Avatar
    Joined
    Oct 2010
    Posts
    33
    Location
    Quote Originally Posted by CharlesH
    hi,

    Check this thread it may help you develope a code for what you want
    http://www.vbaexpress.com/forum/showthread.php?t=34524
    It was one that I worked on with the help of Simon.
    Hey, thanks for the reply...I will look at it straight away!
    What happens if you get scared half to death twice?
    Reply With Quote Reply With Quote
  5. 10-21-2010, 02:28 PM #5
    CharlesH
    CharlesH is offline
    VBAX Regular
    Joined
    Oct 2010
    Posts
    73
    Location
    Check mdmackillop suggestion.
    I added the xlleft to the code, however I did not test.

    Sheets(ws1).Cells(2, 9).Formula = "=VLookup(G2,'" & Sheets(ws1).Range("zz2").End(xlToLeft).Column & "'!G$2:G$" & s2lrow & ", 1," & "False" & ")"
    My Time is valuable. The forum's time is valuable.
    So take time to say "Thanks"

    As with all programs there may be an easier way!
    CharlesH
    Reply With Quote Reply With Quote
  6. 10-21-2010, 02:41 PM #6
    davmec93
    davmec93 is offline
    VBAX Regular davmec93's Avatar
    Joined
    Oct 2010
    Posts
    33
    Location
    Quote Originally Posted by mdmackillop
    Change the data to absolute references or use range names

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC4,'Summary'!R2C1:'Summary'!R580C24,24,FALSE)"
    Please excuse my ignorance but I'm not sure what this formula is supposed to do. RC4...That does not seem to compute in my spreadsheet. Sorry...
    What happens if you get scared half to death twice?
    Reply With Quote Reply With Quote
  7. 10-21-2010, 02:54 PM #7
    mdmackillop
    mdmackillop is offline
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    The code I posted is just an example. It's not clear from your question how the formula and data positions are related. How does it change from day to day? A posted example would assist.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.

    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'
    Reply With Quote Reply With Quote
  8. 10-21-2010, 02:54 PM #8
    davmec93
    davmec93 is offline
    VBAX Regular davmec93's Avatar
    Joined
    Oct 2010
    Posts
    33
    Location
    Quote Originally Posted by CharlesH
    Check mdmackillop suggestion.
    I added the xlleft to the code, however I did not test.

    Sheets(ws1).Cells(2, 9).Formula = "=VLookup(G2,'" & Sheets(ws1).Range("zz2").End(xlToLeft).Column & "'!G$2:G$" & s2lrow & ", 1," & "False" & ")"
    WOW...I'm really feeling inadequete right now. I'm afraid that I don't know how to incorporate this code. I'm not defining something correctly I think.
    What happens if you get scared half to death twice?
    Reply With Quote Reply With Quote
  9. 10-21-2010, 03:08 PM #9
    davmec93
    davmec93 is offline
    VBAX Regular davmec93's Avatar
    Joined
    Oct 2010
    Posts
    33
    Location
    Quote Originally Posted by mdmackillop
    The code I posted is just an example. It's not clear from your question how the formula and data positions are related. How does it change from day to day? A posted example would assist.
    Well today the data that I want the VLOOKUP to return is Column Z
    [VBA]ActiveCell.FormulaR1C1 = "=VLOOKUP(R[0]C[-9],'Summary'!R2C1:'Summary'!R580C24,24,FALSE)"[/VBA]
    tomorrow I will add more data and the column I want return will be AA.[VBA]ActiveCell.FormulaR1C1 = "=VLOOKUP(R[0]C[-9],'Summary'!R2C1:'Summary'!R580C26,26,FALSE)"[/VBA]
    I'm just not sure how to code that.
    What happens if you get scared half to death twice?
    Reply With Quote Reply With Quote
  10. 10-21-2010, 03:13 PM #10
    mdmackillop
    mdmackillop is offline
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Where is the activecell and the lookup value? Do these change? Can you post a workbook showing a sample layout? It makes the solution easier.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.

    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'
    Reply With Quote Reply With Quote
  11. 10-26-2010, 11:09 AM #11
    davmec93
    davmec93 is offline
    VBAX Regular davmec93's Avatar
    Joined
    Oct 2010
    Posts
    33
    Location
    Quote Originally Posted by mdmackillop
    Where is the activecell and the lookup value? Do these change? Can you post a workbook showing a sample layout? It makes the solution easier.
    Here is a sample. The vlookup is on the "Sheet2" tab in column J. It looks up the item number in column A on the Summary sheet and returns the value in the "Qty" column. Today to the Qty value is in coumn F. Tomorrow I will insert more data and the value I need to return will be in column H. I'm sorry if I'm not expaining very well and I really appreciate your attempt to help me.
    What happens if you get scared half to death twice?
    Reply With Quote Reply With Quote
  12. 10-26-2010, 11:14 AM #12
    davmec93
    davmec93 is offline
    VBAX Regular davmec93's Avatar
    Joined
    Oct 2010
    Posts
    33
    Location
    Quote Originally Posted by davmec93
    Here is a sample. The vlookup is on the "Sheet2" tab in column J. It looks up the item number in column A on the Summary sheet and returns the value in the "Qty" column. Today to the Qty value is in coumn F. Tomorrow I will insert more data and the value I need to return will be in column H. I'm sorry if I'm not expaining very well and I really appreciate your attempt to help me.
    Please try this sample instead....sorry
    What happens if you get scared half to death twice?
    Reply With Quote Reply With Quote
  13. 10-26-2010, 01:42 PM #13
    jdub12280
    jdub12280 is offline
    VBAX Regular
    Joined
    Aug 2009
    Posts
    27
    Location
    I'm no expert, however, I would suggest using the recorder to write the formula for you. If I am understanding what you are trying to do correctly, I would use a combination of vlookup + match. If your data shifts to the right constantly, match will return the correct column ( assuming your columns are labeled ). I use vlookup / match all the time so i don't have figure out the location of the data im looking for.

    My two cents...

    -Justin
    Reply With Quote Reply With Quote
  14. 10-26-2010, 02:12 PM #14
    davmec93
    davmec93 is offline
    VBAX Regular davmec93's Avatar
    Joined
    Oct 2010
    Posts
    33
    Location
    Quote Originally Posted by jdub12280
    I'm no expert, however, I would suggest using the recorder to write the formula for you. If I am understanding what you are trying to do correctly, I would use a combination of vlookup + match. If your data shifts to the right constantly, match will return the correct column ( assuming your columns are labeled ). I use vlookup / match all the time so i don't have figure out the location of the data im looking for.

    My two cents...

    -Justin
    I'm not sure the recorder will help me on this one but I do appreciate your input!
    What happens if you get scared half to death twice?
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

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

Forum Rules