August 7th, 2012, 09:22 AM
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:
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.
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
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.
August 7th, 2012, 04:01 PM
I was able to solve this by using VB to SpecialPaste the code in by value. For some reason pasting by keyboard shortcut and then telling it to be by value, or right-click by value pasting does not work.
Just thought I would let everyone know what I found since I figured out a way to fix it.