Hi Guys
I need two favours.
1) How can I auto update the date every month. Eg in march it is 200402, 200401, 200312.
Now in april I want this to be auto updated to 200403, 200402, 200401 and so on for the coming months.
2)Range varies from file to file. I need it to autofill the column for all the corresponding rows in each file. Dont wana change the range manually for every diff file.
eg. In one file there r 220 rows. I need to copy the formula to all those cells. In another file there r 100 rows and so on..need an array I guess
Thanks a lot in anticipation
*****************************************************
Range("K2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-10]=200402,RC[-1],"" "")"
Selection.AutoFill Destination:=Range("K2:K220")
Range("L2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-11]=200401,RC[-2],"" "")"
Selection.AutoFill Destination:=Range("L2:L220")
Range("M2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-12]=200312,RC[-3],"" "")"
Selection.AutoFill Destination:=Range("M2:M220")
Range("N2").Select
ActiveCell.FormulaR1C1 = "=IF(SUM(RC[-3]:RC[-1])=0,RC[-4],"" "")"
Selection.AutoFill Destination:=Range("N2:N220")
I need two favours.
1) How can I auto update the date every month. Eg in march it is 200402, 200401, 200312.
Now in april I want this to be auto updated to 200403, 200402, 200401 and so on for the coming months.
2)Range varies from file to file. I need it to autofill the column for all the corresponding rows in each file. Dont wana change the range manually for every diff file.
eg. In one file there r 220 rows. I need to copy the formula to all those cells. In another file there r 100 rows and so on..need an array I guess
Thanks a lot in anticipation
*****************************************************
Range("K2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-10]=200402,RC[-1],"" "")"
Selection.AutoFill Destination:=Range("K2:K220")
Range("L2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-11]=200401,RC[-2],"" "")"
Selection.AutoFill Destination:=Range("L2:L220")
Range("M2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-12]=200312,RC[-3],"" "")"
Selection.AutoFill Destination:=Range("M2:M220")
Range("N2").Select
ActiveCell.FormulaR1C1 = "=IF(SUM(RC[-3]:RC[-1])=0,RC[-4],"" "")"
Selection.AutoFill Destination:=Range("N2:N220")