August 7th, 2012, 09:22 AM
Join Date: Aug 2012
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:
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
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.
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.