Forum Discussion
Sort data by categories between certain dates.
- Jun 25, 2019
I have a much improved and more flexible VBA routine to replace the one I sent you yesterday:
Sub DateAdjust()
Dim A, B, C, D, E, F, G, H, I, J, K, Sel1, FromTo
FromTo = Selection.Rows.Address(0, 0)
If InStr(FromTo, ",") = 0 Then
A = MsgBox("SELECT CELL IN FIRST ROW OF COLUMN OF DATES TO BE SORTED" & vbCr & vbLf & " AND" & vbCr & vbLf & "WHILE HOLDING CTRL BUTTON" & vbCr & vbLf & _
vbCr & vbLf & "SELECT SECOND CELL IN SAME ROW OF AN EMPTY COLUMN", vbYesNo + vbDefaultButton1, "SORT ALL DATES (OLD & NEW)")
If A = vbNo Then Exit Sub
Else
B = Left(FromTo, InStr(FromTo, ",") - 1)
C = Right(FromTo, InStr(FromTo, ",") - 1)
Range(B).Select
F = ActiveCell.Column
F = Split(Cells(1, F).Address, "$")(1)
G = ActiveCell.Row
Range(Selection, Selection.End(xlDown)).Select
Sel1 = Selection.Rows.Address(0, 0)
D = Selection.Rows.Count
Range(C).Select
E = Selection.Address(0, 0)
H = ActiveCell.Column
H = Split(Cells(1, H).Address, "$")(1)
I = ActiveCell.Row
Range(E & ":" & H & I + D).Select
Selection.NumberFormat = "@"
ActiveCell.Select
For J = 1 To D
Range(F & G + (J - 1)).Select
Call Adjust(H & I + (J - 1))
Next J
Range(E & ":" & H & I + D).Select
Selection.HorizontalAlignment = xlCenter
ActiveCell.Select
End If
End Sub
Sub Adjust(X)
Dim A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q
F = ActiveCell
If Len(F) > 10 Then
F = Date
End If
E = StrReverse(F)
J = InStr(1, E, "/")
K = Right(F, J - 1)
L = year(F)
If J = 3 Then G = L & "/" Else G = Right(F, 4) & "/"
A = InStr(F, "/")
If A < 3 Then B = "0" & Left(F, A - 1) & "/" Else B = Left(F, A - 1) & "/"
C = InStr(A + 1, F, "/")
If C < 5 Then D = "0" & Mid(F, C - 1, 1) Else D = Mid(F, C - 2, 2)
H = Left(F, C)
I = G & B & D
Range(X).Select
ActiveCell = I
End Sub
- calof1Apr 20, 2020Iron Contributor
thank you kindly for the help and assistance, very much appreciated.
This is much easier than how i managing originally.
Thanks again