Macro to insert formula in variable cell

pug

New Member
Joined
Jul 24, 2007
Messages
3
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

pug

New Member
Joined
Jul 24, 2007
Messages
3
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

Well-known Member
Joined
Jun 17, 2005
Messages
7,772
OK,

To insert date and time...
Code:
Sub Insert_Time() 
ActiveCell = Now
End Sub
To insert a formula...
Code:
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
Code:
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:
Code:
Sub Insert_Value
With ActiveCell
.Formula = "=VLOOKUP(""myname"",A:B,2,FALSE)"
.Formula = .Value
End With
End Sub
 

pug

New Member
Joined
Jul 24, 2007
Messages
3
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
 

Forum statistics

Threads
1,181,647
Messages
5,931,210
Members
436,784
Latest member
amuljono

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