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