#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    2
    Rep 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 05:01 PM. Reason: Solved
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    2
    Rep Power
    0

    Solved


    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.

IMN logo majestic logo threadwatch logo seochat tools logo