Convert text to number and change date format in a macro.

Column A is the string 0050, not a date. The date is in B. I can only guess what the references to B and H are supposed to be. Please check the column identifiers in your description.

Is the JDEdwards export file a .txt file, or a .xlsx file exactly like what you attached?

Also, it would be so much easier if you attached the Excel file that has the macro, rather than copying the macro into a Word file and attaching that.

Jeff,

You are correct in your statement about the xlsx "original" file. That is the file I run the macro on. I should have explained that after the Macro is run, the date is then in column "A" and columns "B" and "H" need converting to numbers. That is part of the sort and manipulate data that I spoke of.

The JDEdwards file is exported as the exact xlsx file I have attached.


It is not a "file" that has the macro. This macro is in my Personal.xlbs and can be used whenever needed. The file I use it in changes on a daily basis. I tried to upload module1.bas but that didn't work. Thus, I copied it over into Word and uploaded that file. I'm open for suggestions.

By the way, here is what the file looks like after the macro has been invoked...


Test a.xlsx

I see. You can paste the code directly into your post and mark it with CODE tags. Here is an update to your code that cleans up some macro recorder issues and adds the date cleanup, plus formats columns B and H as text:

Edit: By the way, I tested it on your files.

Sub DataSort()
'
' DataSort Macro
' Sorts and sums data from Scrap Download.
'
' Keyboard Shortcut: Ctrl+Shift+T
'

    Dim i As Long

    Rows("1:1").EntireRow.Delete
    Columns("A:A").EntireColumn.Delete
    Columns("F:F").Insert Shift:=xlToRight
    Columns("E:E").Copy Range("F1")
    
    Range("F:F").Replace What:="2nd-", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("F:F").Replace What:="1st-", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("A1:J27").Sort Key1:=Range("F1"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers
        
    Columns("H:H").EntireColumn.Cut Columns("K:K")
    
    Columns("H:H").EntireColumn.Delete
    
    Columns("J:J").HorizontalAlignment = xlCenter
    
    Range("K5") = "51"
    Range("K6") = "53"
    Range("K7") = "54"
    Range("K8") = "58"
    Range("K9") = "59"
    
    Range("L5").FormulaR1C1 = _
        "=SUMIF(R[-4]C[-6]:R[95]C[-6],RC[-1],R[-4]C[-8]:R[95]C[-8])"
    Range("L6").FormulaR1C1 = _
        "=SUMIF(R[-5]C[-6]:R[94]C[-6],RC[-1],R[-5]C[-8]:R[94]C[-8])"
    Range("L7").FormulaR1C1 = _
        "=SUMIF(R[-6]C[-6]:R[93]C[-6],RC[-1],R[-6]C[-8]:R[93]C[-8])"
    Range("L8").FormulaR1C1 = _
        "=SUMIF(R[-7]C[-6]:R[92]C[-6],RC[-1],R[-7]C[-8]:R[92]C[-8])"
    Range("L9").FormulaR1C1 = _
        "=SUMIF(R[-8]C[-6]:R[92]C[-6],RC[-1],R[-8]C[-8]:R[91]C[-8])"
        
   i = 1
   Do Until Cells(i, "A") = ""
      'Cells(i, "A") = Left(Cells(i, "A"), 6) & "20" & Right(Cells(i, "A"), 2)
      Cells(i, "A") = DateSerial(2000 + CLng(Right(Cells(i, "A"), 2)), _
                                 CLng(Left(Cells(i, "A"), 2)), _
                                 CLng(Mid(Cells(i, "A"), 4, 2)))
      i = i + 1
   Loop
   Range("A:A").NumberFormat = "mm/dd/yy"
   
   ' Format as text
   Range("B:B", "H:H").NumberFormat = "@"
   
   
End Sub

Oops Jeff,

You did exactly what I said I needed. And I have already used that today and works great. However, I gave you some bad info. If you would let me apologize, I would ask one more favor. I need columns "B" and "H" to be converted to numbers. They are already text to start with. If you would be so kind as to fix that for me, I would be forever grateful.


By the way, I am a 5 stringer - bass

Best regards!

@ Jeff and Kenneth:

Thanks for your help!

When I added this...

Dim c As Long
  Application.Calculation = xlCalculationManual
  Application.EnableEvents = False
  
    With Range("B:B", "H:H")
    .Value = .Value
  End With
  
  Application.Calculation = xlCalculationAutomatic
  Application.EnableEvents = True

...to what Jeff had already done, I got the exact results I was looking for.

I appreciate you guys! My daily tasks will go much more smoothly now.