How can time format be retained?

**macyarab** · 02-20-2009, 09:50 AM

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.

**DonkeyOte** · 02-20-2009, 09:53 AM

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

Range(....).NumberFormat = "[hh]:mm"

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

**macyarab** · 02-20-2009, 12:11 PM

Looks to be just what I need I will give it a try and get back.

Much appreciated for the quick reply

**macyarab** · 02-20-2009, 01:26 PM

No luck,

tried as follows :

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

**NBVC** · 02-20-2009, 01:27 PM

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** · 02-20-2009, 01:31 PM

oops my mistake

**DonkeyOte** · 02-20-2009, 03:09 PM

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)

**macyarab** · 02-21-2009, 07:10 AM

You are quite right my code shows just how inept at this I really am. Hopefully I will be able to refine the code as I improve.

what I am trying to do is track traffic flow across a number of data collection points.

Somewhat inelegantly I have got it all working (too much code to place it all here) except that when I pass time from the original spreadsheets through my code via arrays and place it on the new sheet I lose the hh:mm format and end up with time serials .

I can reformat on the final sheet but it takes quite some time to select the cells because they are spread throughout the final sheet.

If I can find out how to format in VBA I could include the code somewhere in one of the numerous sub routines. Youre original suggestion appeared to be along the right lines but my lack of knowledge in adapting the code produced a syntax error, something about incorrect or missing object.

Thank you for taking both the time (no pun intended) and effort to look at this.

Part of the code that sorts the routes and places the masterpoints list on the spreadsheet sheet is shown below Column 1 of the masterpointslist contains the time that loses format during its travel.

Public Sub ROUTETABLE(oldarr)
Dim match As Boolean
Dim Ri As Long

Dim i As Long
Dim Cm As Integer
Dim newarr() As Variant
Dim newrows As Long
Dim newcols As Integer
Dim newCells As Range


match = False
Ri = 1
Cm = 3
ReDim newarr(UBound(oldarr, 1) * 2, 32)

For i = 1 To UBound(oldarr, 1) - 1

        'TRAP debugging
        'If i = UBound(oldarr, 1) - 1 Then
        'MsgBox ("check sub trap and REDIM newarr")
        'End If
        
        
        If match = False Then
        Cm = 3
        newarr(Ri, 1) = oldarr(i, 3)
        newarr(Ri, 2) = oldarr(i, 2)
        newarr(Ri, 3) = oldarr(i, 4)
        newarr(Ri + 1, 3) = oldarr(i, 1)
        
        Else
        newarr(Ri, Cm) = oldarr(i, 4)
        newarr(Ri + 1, Cm) = oldarr(i, 1)
        End If
        
        If oldarr(i, 3) = oldarr(i + 1, 3) And oldarr(i, 3) <> "" Then
        match = True
        Cm = Cm + 1
        
        Else
        'TRAP 2 debugging
        'If Ri > 4500 Then
        'MsgBox ("check" & Ri)
        'End If
        
        
        Ri = Ri + 2
        match = False
        
        End If



Public Sub MASTERLISTTOSHEET()
Dim masterrows As Integer
Dim mastercols As Integer
Dim ArrayCells As Range
Dim x As Variant

masterrows = UBound(masterpointslist, 1)
mastercols = UBound(masterpointslist, 2)


Worksheets("MASTERPOINTSLIST").Activate
Worksheets("MASTERPOINTSLIST").Range("a2").Select
Set ArrayCells = ActiveCell.Range(Cells(1, 1), Cells(masterrows, mastercols))
MsgBox ActiveSheet.Name
x = ActiveSheet.Range(Cells(1, 1), Cells(masterrows, mastercols))
ActiveSheet.Range(Cells(1, 1), Cells(masterrows, mastercols)).Select

ArrayCells.Value = masterpointslist

'Worksheets("MASTERPOINTSLIST").Range(Cells(2, 1), Cells(masterrows, mastercols)).Value = masterpointslist
End Sub

**DonkeyOte** · 02-21-2009, 07:56 AM

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)