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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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
Back
Top