VBA Format solution

[ItalianPlatinum]

I have a VBA that imports a file into the workbook that has the VBA in its own tab for usage.

There is one column that has the column as general format even though it is a date. As a result it is causing a formula to fail as it is looking for a date format vs a non date format. I tried setting the format via VBA .NumberFormat = "mm/d/yyyy" and it does set the format but it requires me to click into each cell to register the format.

My question: is there a fast VBA solution to "click into each cell" to register the format? I noticed even the way it is without me setting the format like stated above and just clicking into the cell it works. The file is quite large with 2500 rows and 15 columns. The column in question is column B and starts on B2 and could go vary each day how many rows.

 

[bebo021999]

Like this:

range("B2:B" & cells(rows.count,"B").end(xlup).row).NumberFormat = "mm/d/yyyy"

 

[ItalianPlatinum]

That I already have -- i need to click into the cell and press enter for the format to apply still. I am trying to find a way to do the press into cell and enter part I have that code down already

 

[bebo021999]

Would suggest in workbook event

Private Sub Workbook_Open()
Application.OnKey "{Enter}", "myDateFormat"
End Sub

and in sheet even

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Set rng = Sheets("Sheet1").Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)
   If Not Intersect(Target, rng) Is Nothing Then
    Call myDateFormat
   End If
End Sub

Sub myDateFormat()
ActiveCell.NumberFormat = "mm/d/yyyy"
Application.OnKey "{Enter}"
End Sub

 

[Marc L]

​

It would be obviously better to fix the bad original import VBA procedure rather than clicking 'on each cell' or needing an 'after' VBA event procedure …​

 

[ItalianPlatinum]

Would suggest in workbook event

Private Sub Workbook_Open()
Application.OnKey "{Enter}", "myDateFormat"
End Sub

and in sheet even

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Set rng = Sheets("Sheet1").Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)
   If Not Intersect(Target, rng) Is Nothing Then
    Call myDateFormat
   End If
End Sub

Sub myDateFormat()
ActiveCell.NumberFormat = "mm/d/yyyy"
Application.OnKey "{Enter}"
End Sub

I will give this a try and get back to you

 

[ItalianPlatinum]

​

It would be obviously better to fix the bad original import VBA procedure rather than clicking 'on each cell' or needing an 'after' VBA event procedure …​

Easier said than done. the file is from a vendor and I have no control over the formatting. I do open the file and import it into my workbook in that conversion i just do a copy all and paste all.

 

[ItalianPlatinum]

Would suggest in workbook event

Private Sub Workbook_Open()
Application.OnKey "{Enter}", "myDateFormat"
End Sub

and in sheet even

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Set rng = Sheets("Sheet1").Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)
   If Not Intersect(Target, rng) Is Nothing Then
    Call myDateFormat
   End If
End Sub

Sub myDateFormat()
ActiveCell.NumberFormat = "mm/d/yyyy"
Application.OnKey "{Enter}"
End Sub

Hi @bebo021999 need a little help. how do I call this in my vba. this is where i copy and paste the data

'Copy File into Compare
Workbooks.Open ("\\Confidential" & Format(Now(), "MM-DD-YY") & " " & "Ns" & ".xml")
Cells.Copy

With Workbooks("Compare").Sheets("NVs")
    .Range("A1").PasteSpecial Paste:=xlPasteValues
    .Range("1:1").AutoFilter
    ''.Protect AllowFormattingColumns:=True, DrawingObjects:=True, Contents:=True, AllowFiltering:=True
End With

Workbooks.Open("\\Confidential" & Format(Now(), "MM-DD-YY") & " " & "Ns" & ".xml").Close SaveChanges:=False

 

[ItalianPlatinum]

I actually found a solution I am all set using the below VBA line to get what I need. Thanks for the help guys.

    .Range("B2:B" & Cells(rows.count, "B").End(xlUp).row).TextToColumns , xlDelimited