• 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...

  • 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

akika

New Member
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:
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:

p45cal

Well-Known Member
You missed one while you were copy/pasting:
https://www.excelguru.ca/forums/showthread.php?9477-how-to-make-a-date-amp-file-name-from-a-sheet-become-a-parameter-in-a-macro&s=419f82021262b47f396f9c8f435aa568


You're probably looking for something like:
Code:
    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:
    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)"
 

p45cal

Well-Known Member
I note that the dates in column A of your attached file have the year 2017.
I also note that some of the hard-coded filenames in the macro have dates in 2018.
Maybe the code isn't finding the right file so is asking you to select it? The file name needs to be exactly correct for it not to open a dialogue box.
 
Top