Printable View
Is it possible to move entire rows (A:I at least) up or down based on either date cell or a status cell (If Statement).
I don't know if I explained that very well.
I have a Toggle Button that Toggles between Edit state and Operational State.
Edit state: I edit everything not worrying about sorting the dates Sequentially.
Operational State triggers the Macro that sorts the Rows based on the Date nearest date being Row1, then Row2 Etc Etc. Leapfrog? ;-) Man it's Hard Work being Lazy..
Say I put a bunch of Dates in a Column Randomly. Would this maybe work to arrange them in descending order? When it inserts the cell it should shift cells down.
Code:Sub ProjSort()
Dim dCell As Range
For Each dCell In Sheets("LD").Range("B6:B82")
If dCell.Offset(1, 0) <> "" Then
Select Case DateDiff("d", Date, dCell.Value)
' Case Is < dCell: ?? 'Cut and Paste to dCell.Offset(2, 0)
End Select
End If
Next
End Sub
why not just sort the entire range by whichever column
try recording a macro
I made this.. It doesn't error, but it isn't doing anything either..
Code:Sub ProjSort()
Dim dCell As Range
For Each dCell In Sheets("LD").Range("B6:B82")
If dCell.Offset(1, 0) <> "" Then
Select Case DateDiff("d", Date, dCell.Value)
Case Is > dCell: Range(dCell.Offset(1, 0)).Select
Application.CutCopyMode = xlCut
Range(dCell.Offset(2, 0)).Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
End Select
End If
Next
End Sub
i have not tested the code you are using, but it seems to compare the datediff with itself, as numberofdays compare to date, so is unlikley to do what you expect
Hmm. Date Diff is comparing the Dcell with the Date. Not the date of Dcell and Dcell offset.
Code:Sub ProjSort()
Dim dCell As Range
For Each dCell In Sheets("LD").Range("B6:B82")
If dCell.Offset(1, 0) <> "" Then
Select Case DateDiff("d", Date, dCell.Offset(1, 0)).Value ' It errors here. No Object.
Case Is < dCell: Range(dCell.Offset(1, 0)).Select
Application.CutCopyMode = xlCut
Range(dCell).Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
End Select
End If
Next
End Sub
Hoabout this?
Code:Private Sub ProjSort()
Dim dCell As Range
For Each dCell In Sheets("LD").Range("B6:B82")
If dCell.Offset(1, 0) <> "" And dCell.Offset(1, 0) < dCell Then
Range(dCell.Offset(1, 0)).Select 'errors here Global Fails
Application.CutCopyMode = xlCut
Range(dCell).Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
End If
Next
End Sub
I found this. Anyone know how to use it?
Date part of argument (excellent for ordering by date)Code:DateValue()
Example: SELECT * from tblPeople ORDER BY DateValue(Review)
Nevermind apparently it's a Function.
This is working somewhat.. I need it to shift cells down when it inserts. ??
Code:Sub ProjSort()
Dim rng As Range
'Dim intRow As Integer, intCol As Integer
For Each rng In Sheets("LD").Range("B6:B82")
If Not IsEmpty(rng) Then
If DateValue(rng.Value) > rng.Offset(1, 0) Then
rng.Offset(1, 0).Cut Destination:=rng
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
End If
End If
Next rng
End Sub
I found it. Too simple.. I didn't realize Excel had a built in Sort. Never used it.
Code:Range("A6:I82").Sort Key1:=Range("B6"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
i suggested that in post #3
haha.. That's what I thought I was doing... I was Recording a macro of me (Manually) sorting the rows by the date column. I didn't know the Data-Sort command was there. ;-)
Pete, How do I make this only fill in the link only if RC[4] has someting there?
Code:For i = 6 To 82
With Cells(i, 1)
.FormulaR1C1 = "=HYPERLINK(""http://eic.mycom.com/eic_drw/qr/"" &RC[4],RC[4])"
With .Font
.Bold = True
.Name = "Arial"
.Size = 12
.Underline = xlUnderlineStyleSingle
.ColorIndex = 5
End With
End With
Next
Nevermind Resolved
http://www.vbforums.com/showthread.php?t=592317