1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

use date & filename data from cells to be in the vba macro code

Discussion in 'VBA Macros' started by akika, Oct 10, 2018.

  1. akika

    akika New Member

    Messages:
    4
    hi,
    pls help.

    i have a main excel 2016 with a vba code in a button.
    when click on btn category A,
    In sheet param, colum A is date and column B is file name.. After input the date DD-MON-YY
    e.g "09-Oct-18" and file name can be anything prefix with the "_09-10-2018.xlsx";in that sheet 'Param'.;;
    I want to use that data entry as parameter in the code instead of opening the macros to change the date each time.;
    How to do it?


    Code (vb):
    If Format(Range("A" & i).Value, "d-mmm-yy") = "09-Oct-18" Then
       Range("B" & i).Select
       ActiveCell.FormulaR1C1 = _
                 "=VLOOKUP(R2C1,'D:\akika\files\[CATEGORY_99_TAS_09-10-2018.xlsx]Sheet1'!R2C1:R30C2,2,FALSE)"

        Range("G" & i).Select
        ActiveCell.FormulaR1C1 = _
            "='D:\akika\working\[consolidate.xlsx]Sheet1'!R4C3"
             
    Else
       If Format(Range("A" & i).Value, "d-mmm-yy") = "15-Oct-18" Then
        Range("B" & i).Select
        ActiveCell.FormulaR1C1 = _
                "=VLOOKUP(R2C1,'D:\akika\files\[CATEGORY_99_TAS_15-10-2018.xlsx]Sheet1'!R2C1:R30C2,2,FALSE)"
     
        Range("G" & i).Select
        ActiveCell.FormulaR1C1 = _
           "='D:\akika\working\[consolidate.xlsx]Sheet1'!R4C3"
       End If
    End If
    Last edited by a moderator: Oct 10, 2018
  2. akika

    akika New Member

    Messages:
    4
  3. p45cal

    p45cal Well-Known Member

    Messages:
    1,231
    You missed one while you were copy/pasting:
    https://www.excelguru.ca/forums/sho...in-a-macro&s=419f82021262b47f396f9c8f435aa568


    You're probably looking for something like:
    Code (vb):
        StrTheDate = Format(Range("A" & i).Value, "dd-mm-yyyy")
        Range("B" & i).FormulaR1C1 = "=VLOOKUP(R2C1,'D:\akika\files\[CATEGORY_99_TAS_" & StrTheDate & ".xlsx]Sheet1'!R2C1:R30C2,2,FALSE)"
    or in one line:
    Code (vb):
        Range("B" & i).FormulaR1C1 = "=VLOOKUP(R2C1,'D:\akika\files\[CATEGORY_99_TAS_" & Format(Range("A" & i).Value, "dd-mm-yyyy") & ".xlsx]Sheet1'!R2C1:R30C2,2,FALSE)"

Share This Page