+ Reply to Thread
Results 1 to 2 of 2

VBA VLOOKUP in workbooks which names changes every week

  1. #1
    Registered User
    Join Date
    10-05-2017
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    1

    VBA VLOOKUP in workbooks which names changes every week

    For a report I want to find values in a different Workbook by using vlookup in a macro. This part works for me but the problem is that the workbook changes in name every week according to the weeknumber. I can't get this to work in a vlookup formula.

    With this formula it works:
    Range("D36").FormulaR1C1 = _
    "=IFERROR(VLOOKUP(""Monday"",'[orders 201739.xlsx]Blad1'!R2C1:R8C2,2,FALSE),0)"

    But my filename changes per week, so the for the next week the formula should need to be:

    Range("D36").FormulaR1C1 = _
    "=IFERROR(VLOOKUP(""Monday"",'[orders 2017340.xlsx]Blad1'!R2C1:R8C2,2,FALSE),0)"

    Is there a way to change this week number in the vlookup using vba?
    Both year (cell R2) and week number (cell E7) are stated in the report workbook.

    Thanks in advance!
    Last edited by bladiebla; 10-06-2017 at 10:58 AM.

  2. #2
    Registered User
    Join Date
    03-15-2010
    Location
    CT
    MS-Off Ver
    Excel 2010
    Posts
    72

    Re: VBA VLOOKUP in workbooks which names changes every week

    You can try something along the lines of:
    Please Login or Register  to view this content.
    just need to change the "Sheet1" to whatever worksheet has the month data in it.

    r/

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 09-06-2016, 04:18 AM
  2. PPT pages as separate files with files names from Excel
    By Amarjeet Singh in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-06-2015, 05:42 AM
  3. Replies: 2
    Last Post: 08-18-2014, 08:39 PM
  4. Two Linked Files But I Change Files Names Each Job
    By barrha0b in forum Excel General
    Replies: 3
    Last Post: 08-30-2013, 11:08 AM
  5. Excel Names in column to text files names to folder
    By excelaron in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-17-2013, 07:22 PM
  6. Comparing Files from Week to Week
    By Ziggyed in forum Excel General
    Replies: 5
    Last Post: 02-08-2009, 06:43 AM
  7. Updating list of names each week
    By jswarb001 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-17-2007, 04:36 AM

Tags for this Thread

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