paste values as numbers not text

Kristen
Assuming the cell that you want to format is A1, try adding this line of code :-

Range("A1").Value=Range("A1").Value

Celia

I am still not completely clear as to how you are arriving at your data. but, try doing this after you have ran your paste macro: highlight your column of text information, then go to Data->Text to Columns...-> and follow the wizard. On step 3, choose Date. (if date does not work, choose General and see if it works.) I think the problem with what you are doing might have something to do that in order for a Time to show up, Excel requires a date also.

Cheers,

Thomas

Interesting delimma. Perhaps you can share more information such as the formatting of the time. I am not quite sure what (eg 35:12:11) means. Is it hours, minutes, seconds? if so, what is the date. For when I copy this (eg 35:12:11) to Excel, it give me a date also (01/01/1900 11:12:11 AM).

On way you might want to try getting around this problem is have your data (the results of your formula) seperated into different columns, then CONCATENATE it all back together in your new worksheet.

Cheers,

Thomas I was wondering if you could help..... I have a macro which copies a column of cumulative times (eg 35:12:11) from one sheet and pastes to another as values (which is necessary as I don't want to paste the formula). However it pastes as text (eg 00:12:35) so I am unable to run calculations on those values. If I select the field, hit F2 and then hit enter it converts it to a number format (eg 0:12:35) - which is what I want but I can't do it manually as there are potentially thousands of records..... cheer, kristen

I have a macro which copies a column of cumulative times (eg 35:12:11) from one sheet and pastes to another as values (which is necessary as I don't want to paste the formula). However it pastes as text (eg 00:12:35) so I am unable to run calculations on those values. If I select the field, hit F2 and then hit enter it converts it to a number format (eg 0:12:35) - which is what I want but I can't do it manually as there are potentially thousands of records..... cheer, kristen

Kristen
Try formatting the receiving cells in the format you want (presumably h:mm:ss)- you can do this in your macro.
Celia

Thomas
The cell that displays 35:12:11 is probably formatted as [h]:mm:ss.
Or could be as text.
Celia

Interesting delimma. Perhaps you can share more information such as the formatting of the time. I am not quite sure what (eg 35:12:11) means. Is it hours, minutes, seconds? if so, what is the date. For when I copy this (eg 35:12:11) to Excel, it give me a date also (01/01/1900 11:12:11 AM). On way you might want to try getting around this problem is have your data (the results of your formula) seperated into different columns, then CONCATENATE it all back together in your new worksheet. Cheers, Thomas : I was wondering if you could help..... : I have a macro which copies a column of cumulative times (eg 35:12:11) from one sheet and pastes to another as values (which is necessary as I don't want to paste the formula). However it pastes as text (eg 00:12:35) so I am unable to run calculations on those values. If I select the field, hit F2 and then hit enter it converts it to a number format (eg 0:12:35) - which is what I want but I can't do it manually as there are potentially thousands of records..... : cheer, : kristen

What do you know! The text to columns worked... never used it or even noticed it before!

Thanks!! I am still not completely clear as to how you are arriving at your data. but, try doing this after you have ran your paste macro: highlight your column of text information, then go to Data->Text to Columns...-> and follow the wizard. On step 3, choose Date. (if date does not work, choose General and see if it works.) I think the problem with what you are doing might have something to do that in order for a Time to show up, Excel requires a date also. Cheers, Thomas

yep the format is [h]:mm:ss.

Celia - I've tried setting the cell format in the macro but when I paste as values it ignores the cell format and paste's the cell contents as text while retaining the cell format. Thomas

Kirsten
Post your code so I can check.
Celia