VBA: Re-Ordering Columns, Some non-date data turns into dates?

[LNG2013]

Hey everyone!

I am using a bit of VBA code to help me re-order the columns of my data (code below). However some of the data that is pure numbers (eg.1) is getting formatted into a date: 1/1/1900 0:00 . I am not sure if this is being caused by data the previously occupied those columns, eg the Time_stamp field, or if it is just a weird fluke in excel.

The macro first opens a set of data (csv), and copies it to a new workbook. It then sorts the columns. I am wondering if there is coding where by I can have it autoformat those fields to text only prior to copying and if that will fix the solution?

Sub Edit()
    Call OpenBook
    Call Order
End Sub
'
'
'
'
Sub OpenBook()
' 
Workbooks.Add
    Workbooks.Open Filename:=ThisWorkbook.Path & "\Data.csv"
    ActiveSheet.UsedRange.Copy
    Windows("Book1").Activate
    ActiveSheet.Paste
'
End Sub
 
 
 
    Sub Order()
'
'
' This Macro places the columns in the correct order 
'
 
Dim rng As Range
Dim i As Integer
Dim J As Integer
Dim Temp
Dim nams As Variant
Dim F
Dim Dex As Integernams = Array("ItemID", "FirstName", "LastName", "Address", "ValueA11", "ValueB11", "ValueC11", "ValueD11", "ValueE11", "ValueA21", "ValueB21", "ValueC21", "ValueD21", "ValueE21", "ValueA31", "ValueB31", "ValueC31", "ValueD31", "ValueE31", "ValueA41", "ValueB41", "ValueC41", "ValueD41", "ValueE41", "ValueA51", "ValueB51", "ValueC51", "ValueD51", "ValueE51",  "Time_Stamp", "Week", "Month", "Year")
Set rng = Range("A1").CurrentRegion
For i = 1 To rng.Columns.Count
    For J = i To rng.Columns.Count
        For F = 0 To UBound(nams)
            If nams(F) = rng(J) Then Dex = F: Exit For
        Next F
        If F < i Then
            Temp = rng.Columns(i).Value
            rng(i).Resize(rng.Rows.Count) = rng.Columns(J).Value
            rng(J).Resize(rng.Rows.Count) = Temp
        End If
    Next J
Next i
End Sub

 

[MickG]

Try altering the code as folows:-

 Temp = rng.Columns(i).value
            rng(i).Resize(rng.rows.Count) = rng.Columns(J).value
            rng(i).Resize(rng.rows.Count).NumberFormat = "0"
            rng(J).Resize(rng.rows.Count) = Temp
            rng(J).Resize(rng.rows.Count).NumberFormat = "0"

Mick

 

[Jonmo1]

Try using the built in Sort Function.
It can sort by rows instead of columns...

Assuming your headers are in Row 1..

Sub test()
Dim MyHeaders As Variant, i As Long
 
MyHeaders = Array("ItemID", "FirstName", "LastName", "Address", "ValueA11", "ValueB11", "ValueC11", "ValueD11", "ValueE11", "ValueA21", "ValueB21", "ValueC21", "ValueD21", "ValueE21", "ValueA31", "ValueB31", "ValueC31", "ValueD31", "ValueE31", "ValueA41", "ValueB41", "ValueC41", "ValueD41", "ValueE41", "ValueA51", "ValueB51", "ValueC51", "ValueD51", "ValueE51", "Time_Stamp", "Week", "Month", "Year")
Rows(1).Insert
For i = LBound(MyHeaders) To UBound(MyHeaders)
    Cells(1, i + 1).Value = Application.Match(Cells(2, i + 1).Value, MyHeaders, 0)
Next i
With ActiveSheet.UsedRange
    .Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
        DataOption1:=xlSortNormal
End With
Rows(1).Delete
End Sub

Hope that helps.

 

[LNG2013]

This worked but one of my fields is a a date and time field (Time_Stamp), I am throwing in some code to find the specific column and change it back to date and time.

Try altering the code as folows:-

 Temp = rng.Columns(i).value
            rng(i).Resize(rng.rows.Count) = rng.Columns(J).value
            rng(i).Resize(rng.rows.Count).NumberFormat = "0"
            rng(J).Resize(rng.rows.Count) = Temp
            rng(J).Resize(rng.rows.Count).NumberFormat = "0"

Mick

 

[jasmith4]

LN2013, Your first choice is right -- Excel is auto-formatting. When you open a text file, it's just like creating a new workbook and typing in all the text: if text looks like a date, Excel converts it to a number (using the DateValue function) and formats that number as a date. Same with numbers, especially percents and scientific notation: Excel converts it to a raw number (e.g., dividing the percent by 100) and formats it so it looks like the original text. All these become numeric-type cells, not text-type cells. Of course, if you enter an initial apostrophe or a bunch of letters, the cell won't be auto-formatted, so its format will be General, and it will be a label-type cell containing the text.
All this happens as you open the CSV, before your code comes along that shuffles around the values. It shuffles *only* the values -- anything that any auto-formatting has done remains. So if the CSV's first column has dates, column A will be formatted as a date, but then when you put new values into column A, they're expressed as dates, because no auto-formatting takes place and your code runs.

If you want to shuffle formats as well as values, take a look at the Insert method of the Range object.

 

[LNG2013]

MickG-

Is there a way to do this with omitting one of the columns?

Try altering the code as folows:-

 Temp = rng.Columns(i).value
            rng(i).Resize(rng.rows.Count) = rng.Columns(J).value
            rng(i).Resize(rng.rows.Count).NumberFormat = "0"
            rng(J).Resize(rng.rows.Count) = Temp
            rng(J).Resize(rng.rows.Count).NumberFormat = "0"

Mick

 

[MickG]

Try this code:-
NB:-The array Ray is now Two dimensional with the second dimension being for Formats.
Extend the Array (change "Redim" line accordingly)as required and Change the Formats to match your data.

[COLOR=navy]Sub[/COLOR] MG31May46
'[COLOR=green][B]Sort columns by Array "Nams"[/B][/COLOR]
[COLOR=navy]Dim[/COLOR] rng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] i [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] J [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] temp, ftemp
[COLOR=navy]Dim[/COLOR] nams [COLOR=navy]As[/COLOR] Variant
[COLOR=navy]Dim[/COLOR] F
[COLOR=navy]Dim[/COLOR] Dex [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
ReDim nams(0 To 6, 1 To 2)
nams(0, 1) = "ItemID": nams(0, 2) = "General"
nams(1, 1) = "FirstName": nams(1, 2) = "dd/mm/yyyy"
nams(2, 1) = "LastName": nams(2, 2) = "General"
nams(3, 1) = "Address": nams(3, 2) = "General"
nams(4, 1) = "Week": nams(4, 2) = "dd/mm/yyyy"
nams(5, 1) = "Month": nams(5, 2) = "General"
nams(6, 1) = "Year": nams(6, 2) = "General"
[COLOR=navy]Set[/COLOR] rng = Range("A1").CurrentRegion
rng.NumberFormat = "General"
[COLOR=navy]For[/COLOR] i = 1 To rng.Columns.Count
    [COLOR=navy]For[/COLOR] J = i To rng.Columns.Count
        [COLOR=navy]For[/COLOR] F = 0 To UBound(nams)
            [COLOR=navy]If[/COLOR] nams(F, 1) = rng(J) [COLOR=navy]Then[/COLOR] Dex = F: [COLOR=navy]Exit[/COLOR] For
        [COLOR=navy]Next[/COLOR] F
        [COLOR=navy]If[/COLOR] F < i [COLOR=navy]Then[/COLOR]
            temp = rng.Columns(i).value
            ftemp = rng.Columns(i).NumberFormat
            rng(i).Resize(rng.rows.Count).NumberFormat = nams(F, 2)
            rng(i).Resize(rng.rows.Count) = rng.Columns(J).value
            rng(J).Resize(rng.rows.Count) = temp
            rng(J).Resize(rng.rows.Count).NumberFormat = ftemp
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] J
[COLOR=navy]Next[/COLOR] i
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick