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.