Macro to insert formula in variable cell

[pug]

I need a macro to insert =now() into whichever cell is active and then to turn the formula into an absolute.

I tried recording a macro but had two problems:

The macro would only work in the cell that was active at the time of recording and it put the date and time of when the macro was created and not the date and time when the macro was run.

I recorded the keystrokes =now()f9Enter where f9 was the f9 key

any help will be appreciated

 

[njimack]

Sub Insert_Time()
ActiveCell = Time
End Sub

 

[pug]

Thanks Neil

That got the time in but I need the date & time

I also need to know how to do it with other formula, like how do you include the formula into the macro as apposed to the results of the formula

cheers

pug

 

[njimack]

OK,

To insert date and time...

Sub Insert_Time() 
ActiveCell = Now
End Sub

To insert a formula...

Sub Insert_Formula()
ActiveCell.Formula = "=SUM(A:A)"
End Sub

Note that formulae with quote marks ("") can be tricky in VBA, e.g the formula =VLOOKUP("myname",A:B,2,FALSE) would become

Sub Insert_formula()
ActiveCell.Formula = "=VLOOKUP(""myname"",A:B,2,FALSE)"
End Sub

To insert a formula as a value, there are several methods. Probably the easiest for someone new to VBA is as follows:

Sub Insert_Value
With ActiveCell
.Formula = "=VLOOKUP(""myname"",A:B,2,FALSE)"
.Formula = .Value
End With
End Sub

 

[pug]

Neil

You are a gem - thanks for that.

I started using Lotus123 in 1979 and could make it sing and dance but Excel's macro language I find most difficult.

But you have proved you can teach an old dog new tricks.

again, thanks

Pug