How can time format be retained?

I am struggling to cope with time (aren't we all).
I pick up a range of cells formatted on the worksheet as hh:mm. Put these into a macro and do some sorting. Then I place them on another worksheet but they arrive as time serials. The time serial appears as soon as the cells are placed in the macro.

How can I retain the hh:mm format from one worksheet to the next without having to manually reformat the cells?

Is there a VBA format method for individual array elements.

Any help gratefully received.

Without seeing your code it's hard to comment but in short:

Please Login or Register  to view this content.

(or paste special with formats... above may be simpler... unknown)

No luck,

tried as follows :

Please Login or Register  to view this content.

Sub FillPoint(Temp, point, POINTXX, SURVLOC)

Dim c As Long
Dim R As Long
Dim RA As Range
Dim Rng As Range
Dim i As Long

Set Rng = Sheets(point).Range(Temp)

For Each RA In Rng.Areas





R = R + RA.Rows.Count
Next RA




ReDim POINTXX(R, 4)

For Each RA In Rng.Areas
For R = 1 To RA.Rows.Count


i = i + 1
For c = 1 To RA.Columns.Count

POINTXX(i, c) = RA.Cells(R, c)

'need to write code to force time format of column 1

'Range("POINTXX(i, 1)").NumberFormat = "[hh]:mm"

Format(RA.Cells(R, 1)).Value = "hh:mm"

' Range("Cells(R, 1)").NumberFormat = "[hh]:mm"

'need to write code to force time format of column 1

POINTXX(i, 4) = SURVLOC




Next c
Next R
Next RA

''''''''''''''''''''' REMOVES BLANK ROWS (END OF PAGE ONLY)
CUTENDPAGEBLANK POINTXX
''''''''''''''''''''' REMOVES BLANK ROWS (END OF PAGE ONLY)


End Sub

Please Login or Register  to view this content.

[/CODE]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

macyarab,

I'm struggling to fathom what you're code is meant to be doing...

I can determine that you are invoking this from elsewhere passing a worksheet, a range and 2 arrays (all defined as variants) ... you're then creating a range based off the sheet & range iterating through that ranges various areas to determine how many rows are contained within... redefining the first of the variant arrays... then populating the first variant array (POINTX) by iterating through each range area (row then column) however from there on I'm lost... are you then trying to write back the contents of the array somewhere else ?

So in short... what is it you're trying to do...?

I suspect you won't be surprised to hear that I think you may be able to achieve the same thing with far less aggravation... (& code)

Sorry to be a pain but I'm utterly lost - I don't know what the new code is meant to represent - the first routine is incomplete and the second appears to refer to a Global variable masterpointlist which is not detailed anywhere in your code... also I can't fathom how this ties out to the code you provided in your penultimate post.

I understand this is frustrating as it may seem obvious to you but it is equally frustrating on our part as we can't see the data or all of your code in context... are you able to provide a sample of your file ?
(dummy the data if confidential obviously)

Which could be coded as per the below - with additional format line:

Please Login or Register  to view this content.

I guess my point is I very much doubt you need to use an array to store the values you want to copy... even if your range was non contiguous you can easily cycle the areas within and paste to next blank row etc... you're putting together a complex sub routine here and I think you're making life more difficult for yourself than need be ... not a bad thing in terms of a learning curve but frustrating at the same time I'm sure ;-) ... in the last code you posted you populate the array PointXX but it never gets used... you adopt a similar approach to the above with your use of point1A

Time is a decimal value... noon = 0.5 ... 6am = 0.25 etc... the cell format is just that - it formats the decimal value to appear a certain way... so when referencing time you are storing decimal values... if you want to store as "12:00" you will need to store as actual text strings which would be a bit odd.

I'm not going to get into it again but.... I remain very sceptical that you need to use an Array in your code in the first instance...