Changing/retaining format of cells

[chamster]

I tried the following code
[VBA]
Union(.Range(.Cells(2, 8), .Cells(periods + 1, 8)), .Range(periods + 3, 8, 2 * periods + 3, 8)).Select
Selection.NumberFormat = "hh:mm,@"
[/VBA]
as well as a not so sophisticated version
[VBA]
.Range(.Cells(2, 8).Cells(2 * periods + 3, 8)).Select
Selection.NumberFormat = "hh:mm,@"
[/VBA]
and in both cases the area gets selected BUT the format doesn't stay (or isn't changed - i don't know which. Why and how do i kill it?

On a very similar subject - i try to copy a range and paste it in INCLUDING the formatting. What i do is the following.
[VBA]
With ActiveSheet
.Range(.Cells(1, 1), .Cells(periods, 1)).Select
Selection.Copy
.Range(.Cells(2, 8), .Cells(periods + 1, 8)).Select
.Paste
.Range(.Cells(periods + 3, 8), .Cells(2 * periods + 3, 8)).Select
.Paste
.Range(.Cells(2, 8).Cells(2 * periods + 3, 8)).Select
Selection.NumberFormat = "hh:mm,@"
End With
[/VBA]

For some reason (which i'm sure i'll be quickly made aware of soon) the formatting dosn't follow the copy process. How do i enforce it?

[Charlize]

You could try this one (not tested)[VBA]With ActiveSheet
.Range(.Cells(1, 1), .Cells(periods, 1)).Copy
.Range(.Cells(2, 8), .Cells(periods + 1, 8)).PasteSpecial xlPasteAll
.Range(.Cells(periods + 3, 8), .Cells(2 * periods + 3, 8)).Copy
.Range(.Cells(2, 8).Cells(2 * periods + 3, 8)).PasteSpecial xlPasteAll
End With[/VBA]

[chamster]

I'll try that right away. Thanks!

Now, to the first part of the question. How come the formatting doesn't work the way i'd expect? Using your approach i can solve my problem for today but i like to feel that i'm the one in control even tomorrow, not the stupid piece of silicon...

Also, i just realized that i'm changing the format anyway. In the original cells one can see yyyy-mm-dd hh:mm:ss but i only wish to present the hh:mm format. So, the tomorrow issue has just become todays issue. More suggestions, please.

[rory]

wHAT

[rory]

Let's try that again!

What is the format supposed to do? That is, apparently, the equivalent of the built-in time format hh:mm in the Catalan region.

[chamster]

Perhaps i didn't do a great job explaining my pains. Here it goes:

I have a column on form
2007-05-02 07:00:00
2007-05-02 07:05:00
2007-05-02 07:10:00
2007-05-02 07:15:00
...
2007-05-08 09:40:00
2007-05-08 09:45:00
2007-05-08 09:50:00
2007-05-08 09:55:00
and since i'm cutting the data placing each new day TO THE RIGHT (as in "even-slice-and-place-nice") i only need the labels for hours, not the days/months/years.

So, i copied the time stamps of the first day and pasted them in front of the newly created tables of slices. Now, i need to ditch the date part retaining only time part from it.

As i do it, i get "########" in the cells. The data is right because i can then pick the format manually and everything is right. The problem is that i need it to be automated...

[rory]

In that case you only want "hh:mm" as a number format and it should work, assuming your dates are actually stored as dates.

[chamster]

Are you saying that i should skip the comma and at characters? What do they do?

[rory]

I'm assuming that your system uses the comma to separate the format sections (mine uses semicolons). The @ symbol is the text symbol.
Number formats are broken down into up to 4 sections:
1. Positive numbers
2. Negative numbers
3. Zero values
4. Text values