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

[akika]

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?

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

 

[akika]

Attached sample.. pls help
cross link
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

 

[p45cal]

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:

    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:

    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)"

 

[akika]

hi,
i tried the code but each time it opening the dialog box to select file.
can u pls chk attach file?

 

[p45cal]

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.