Results 1 to 3 of 3

Thread: how to make a date & file name from a sheet become a parameter in a macro

  1. #1
    Seeker akika's Avatar
    Join Date
    Aug 2018
    Posts
    7
    Articles
    0
    Excel Version
    2016

    how to make a date & file name from a sheet become a parameter in a macro



    Register for a FREE account, and/
    or Log in to avoid these ads!

    hi,
    pls help.
    i have a main excel 2016 with a vba code in a button.
    In sheet param, column 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.; How to do it?


    Code:
     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
    a
    Last edited by Pecoflyer; 2018-10-10 at 07:31 AM. Reason: added code tags

  2. #2
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,709
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Hi
    in the future please wrap your code with code tags ( click -Go advanced - select code - click the #button). I added the tags on your first post for you. Thx
    Thank you Ken for this secure forum.

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,733
    Articles
    0
    Excel Version
    365
    cross posted without links:
    https://chandoo.org/forum/threads/us...ro-code.39990/
    https://www.mrexcel.com/forum/excel-...ter-macro.html
    https://www.excelforum.com/excel-pro...n-a-macro.html

    akika, for your information, you should always provide links to your cross posts.
    This is a requirement, not just a request.
    If you have cross posted at other places, please add links to them too.
    Why? Have a read of http://www.excelguru.ca/content.php?184
    Last edited by p45cal; 2018-10-10 at 04:03 PM.

Tags for this 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
  •