Option Explicit
Sub FixCSV()
Dim sCSV As String
Dim wbCSV As Workbook
Dim wsCSV As Worksheet
Dim rCSV As Range, rCSV1 As Range
Dim i As Long, j As Long
sCSV = Application.GetOpenFilename("ERP File, *.CSV")
If sCSV = "False" Then Exit Sub
Application.ScreenUpdating = False
Workbooks.Open Filename:=sCSV
Set wbCSV = ActiveWorkbook
Set wsCSV = ActiveSheet
With wsCSV ' Guessing
.Cells(1, 1).Value = "Date"
.Cells(1, 2).Value = "Invoice"
.Cells(1, 3).Value = "Store"
.Cells(1, 4).Value = "Product"
.Cells(1, 5).Value = "Qty"
.Cells(1, 6).Value = "Cost"
.Cells(1, 7).Value = "InvCred"
.Cells(1, 8).Value = "Something"
.Cells(1, 9).Value = "Counter"
Set rCSV = .Cells(1, 1).CurrentRegion
'save original order
For i = 1 To rCSV.Rows.Count
.Cells(i, 9).Value = i
Next i
Set rCSV1 = rCSV.Cells(2, 1).Resize(rCSV.Rows.Count - 1, rCSV.Columns.Count)
With .Sort
.SortFields.Clear
.SortFields.Add Key:=rCSV1.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=rCSV1.Columns(3), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=rCSV1.Columns(7), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.SetRange rCSV
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
With rCSV
For i = 2 To .Rows.Count
If .Cells(i, 7).Value = "C" Then ' CREDIT?
j = i
'same store and same date
Do While (.Cells(j, 3).Value = .Cells(i - 1, 3).Value) And _
(.Cells(j, 1).Value = .Cells(i - 1, 1).Value)
.Cells(j, 2).Value = "'9" & .Cells(i - 1, 2).Value ' add leading 9
.Cells(j, 7).Value = "-C" ' add marker
j = j + 1
Loop
End If
Next i
Call .Columns(7).Replace("-C", "C", xlWhole)
End With
With wsCSV
With .Sort
.SortFields.Clear
.SortFields.Add Key:=rCSV1.Columns(9), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange rCSV
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'get rid of order column
.Columns(9).Delete
'row 1 was originally blank
.Rows(1).Resize(1, 8).Value = " "
End With
i = InStrRev(wbCSV.Name, ".")
sCSV = wbCSV.Path & Application.PathSeparator & Left(wbCSV.Name, i - 1) & "-out.csv"
Application.DisplayAlerts = False
On Error Resume Next
Kill sCSV
On Error GoTo 0
Application.DisplayAlerts = True
wbCSV.SaveAs sCSV, xlCSV
wbCSV.Close False
Application.ScreenUpdating = False
MsgBox "CSV Converted as " & sCSV
End Sub