PDA

View Full Version : How do you create a VBA formula code based on Filename?



genracela
06-24-2010, 09:50 PM
I have a match index formula that will look up data from another file, but the filename changes everyweek.

How will I create a VBA formula code if filename changes?

Below is my sample formula line, usually only the date on the filename changes (highlighted in red").

Range("A7:A" & lRow).Formula = "=INDEX('G:\SCP\Consolidated\Product Supply Report\2010\Export Product Supply\Malaysia\[CAMPAIGNLY MAJOR OFFER SUPPORTABILITY_CFT_062310.xls]PSR'!H$7:H$65536,MATCH($F1,'G:\SCP\Consolidated\Product Supply Report\2010\Export Product Supply\Malaysia\[CAMPAIGNLY MAJOR OFFER SUPPORTABILITY_CFT_062310.xls]PSR'!$F$7:$F$65536,))"

Gollem
06-24-2010, 10:53 PM
dim strDay as string

strDay = "062310"
Range("A7:A" & lRow).Formula = "=INDEX('G:\SCP\Consolidated\Product Supply Report\2010\Export Product Supply\Malaysia\[CAMPAIGNLY MAJOR OFFER SUPPORTABILITY_CFT_" & strDay & ".xls]PSR'!H$7:H$65536,
MATCH($F1,'G:\SCP\Consolidated\Product Supply Report\2010\Export Product Supply\Malaysia\[CAMPAIGNLY MAJOR OFFER SUPPORTABILITY_CFT_" & strDay & ".xls]PSR'!$F$7:$F$65536,))"


Use a string variable and create a script to calculate the day.

Hope this gives you an idea.

genracela
06-24-2010, 10:56 PM
Can I do
strDay = range("A1") ???

Gollem
06-24-2010, 11:00 PM
Yes that should work:



strDay = range("A1").value


You should test it.
Let me know the outcome.

genracela
06-25-2010, 12:57 AM
Thanks! I'll let you know what happened when I finish my project!


Thanks again!!!

mdmackillop
06-25-2010, 05:08 AM
Please use line breaks _
in long lines of text.