August 7th, 2012, 09:22 AM
|
|
Registered User
|
|
Join Date: Aug 2012
Posts: 2
Time spent in forums: 1 h 46 m 5 sec
Reputation Power: 0
|
|
|
Solved: Excel, trying to update cell/cell format through VB
Okay so I have data that is currently being pasted into Excel from Access. In this data is a column of End dates/times.
When it gets pasted into Excel it loses its Date/Time format and as far as I can tell becomes just text. Now if I click into the Cell as though I was going to edit it and exit (not changing anything) it will automatically update to a Date/Time format which is what I need.
Currently I just finished trying to use a macro with VB to format these cells as Date/Time cells using this code:
Code:
Dim row
row = Cells(Rows.Count, "B").End(xlUp).row
While (Cells(row, "F").Value <> 0)
ActiveSheet.Cells(row, "F").NumberFormat = "m/d/yyyy h:mm"
row = row + 1
Wend
Although this does change the cells "Format" it doesn't actually change the data like I need it to, unless I do as I mentioned earlier and go through each and every cell as though I am going to edit it and then exit.
The alternitive is tedious as I am pasting in up to 200 new rows at a time.
( ::Note:: The code cycles through all of the cells just fine without throwing any errors but for some reason just doesn't change the content to be in a date/time format of new un-"edited" cells it will change the ones I have previously entered into and exited)
Last edited by AlluvialSin : August 7th, 2012 at 04:01 PM.
Reason: Solved
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
