Insert formula at the time of running the macro

[VARGEESH]

Hi,


I am running a macro coding in an excel file.


In that excel file Column "A"(Old date) has past date.


Also there are three blank columns named as:-


Column B = "TODAY"


Column C = "AGEING"


Column D = "AGEING > 7 DAYS".


When i run the macro it should write the formulas


In Col B ---> [=today()]


In Col C ---> [=B-A]


In Col D ---> [=IF(M2>7,"1",0)]


Note:Sometimes the excel sheet may have 1000 rows of data. Like that, it may have 5000 rows of data.


Kindly advice me.


Thanks!

 

[Krishnakumar]

Hi


try

[pre]

Sub kTest()

Dim r   As Long

r = Range("a" & Rows.Count).End(3).Row

With Range("b2:b" & r)
.Value = Date
.Offset(, 1).FormulaR1C1 = "=rc[-1]-rc[-2]"
.Offset(, 2).FormulaR1C1 = "=(rc[-1]>7)+0"    'I gues you are referring column c rather than col M.
.Offset(, 1).Resize(, 2) = .Offset(, 1).Resize(, 2).Value
End With

End Sub

[/pre]

Kris

 

[VARGEESH]

Hi Krishna,


Thank you for your help krishna.


It is working.


Regards,

Vargeesh