Novice Excel Macro Question

carl swanstrom

New Member
Joined
Oct 27, 2002
Messages
2
I have tons of data downloaded from a new process. Column B contains the time with the seconds in decimal format (eg. 00:12:08.310
). Excel will not allow me to reformat the cells and my boss wants graphs with time he can understand. I accidently found that if I hit "edit" (F2) and then enter, the value is now in a format that Excel will let me reformat. GREAT! However, I can not figure out how to write a macro that does this for 1440 rows. Making sure the I am using relative references, I try recording a macro by hitting F2 then enter five times. I run the macro and it does aexactly what I need for five rows. However, if I try to run it again for the next five rows, it puts the values for the first five rows in, Please help. I am sure this is very simple, but I am very new to writing successful macros.

Thanks for any help.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
This should work, but make sure you back up your data first.

In a spare cell type 0 (zero) and press Enter. With that cell still selected choose Edit, Copy from the menu. Select all the cells containing times and choose Edit, Paste Special from the menu. Under Operation check Add and click OK.
 

carl swanstrom

New Member
Joined
Oct 27, 2002
Messages
2
Andrew,
Thank you so much for the elegant but simple solution. It works like a charm and I even recorded a macro that does the addition and reformats the cells for the time format I need. Wish I knew why it works, but I will be happy just having a solution. Wish I had found this site before. It seems like the percentage of Excel users that ever write macros is small. I am really excited about having a place to go for help.
Again, thanks to Andrew and Mr Excel for a very valuable WEB site.

Carl
 

Forum statistics

Threads
1,144,363
Messages
5,723,919
Members
422,527
Latest member
JayTheKaz

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