Auto Date Change and Array function for range!

excelguy

New Member
Joined
Mar 10, 2003
Messages
27
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")
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

CDICKENS

Active Member
Joined
Mar 24, 2002
Messages
498
Try this for the auto range update. Get your last row for the column, and then use that value in the AutoFill destination.
Code:
lstrow = Sheets("Sheet1").Range("K65536").End(xlUp).Row

Range("K2").Select 
ActiveCell.FormulaR1C1 = "=IF(RC[-10]=200402,RC[-1],"" "")" 
Selection.AutoFill Destination:=Range("K2:K" & lstrow)

Thanks,

Chuck
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,491
Messages
5,764,681
Members
425,229
Latest member
Rashid mahmood

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top