Simple Request

[00fil00]

Hi guys,

I have made a small automated "to do" list to keep on track of things at work. I want to be able to push a macro button built onto the page and the tasks I have written will organize themselves into priority, much like a multi-stage filter but at the push of a button.

I have priority A, B, C in column F
I have Date Raised in column D
Column E has Due By - which is automatically populated from detecting the priority (A B C) and then adding +7 days, +21 days, and + 30 days onto the Date Raised.

The piece of code of interest is in the first paragraph section. I have this working but it populates the due by date even when the date raised is blank and produces a fake date that is random - like january. These fake dates are pushed to the top of the list for some reason.

How can I get this code to skip filling in the Due Date (column E) if the raised date (Column D) cell is blank?

Sub SortMultipleColumns()

Dim SrchRng As Range, cel As Range


Set SrchRng = Range("F3:F599")



For Each cel In SrchRng

If InStr(1, cel.Value, "A") > 0 Then
cel.Offset(0, -1).Value = cel.Offset(0, -2).Value + 7
End If

If InStr(1, cel.Value, "B") > 0 Then
cel.Offset(0, -1).Value = cel.Offset(0, -2).Value + 21
End If

If InStr(1, cel.Value, "C") > 0 Then
cel.Offset(0, -1).Value = cel.Offset(0, -2).Value + 30
End If



Next cel


With ActiveSheet.Sort


.SortFields.Clear

.SortFields.Add Key:=Range("E2"), Order:=xlAscending
.SortFields.Add Key:=Range("F2"), Order:=xlAscending
.SortFields.Add Key:=Range("D2"), Order:=xlDescending


.SetRange Range("B2:H199")
.Header = xlYes
.Apply

End With


Dim rCheck As Range
Dim rHide As Range
Dim rCheckCell As Range

Set rCheck = ActiveWorkbook.ActiveSheet.Range("B3:B599")
rCheck.EntireRow.Hidden = False

For Each rCheckCell In rCheck.Cells
If InStr(1, rCheckCell, "Closed", vbTextCompare) > 0 Then
If Not rHide Is Nothing Then Set rHide = Union(rHide, rCheckCell) Else Set rHide = rCheckCell
End If
Next rCheckCell

If Not rHide Is Nothing Then rHide.EntireRow.Hidden = True

End Sub




Sub SortMultipleColumns()

Dim SrchRng As Range, cel As Range


Set SrchRng = Range("F3:F599")



For Each cel In SrchRng

If InStr(1, cel.Value, "A") > 0 Then
cel.Offset(0, -1).Value = cel.Offset(0, -2).Value + 7
End If

If InStr(1, cel.Value, "B") > 0 Then
cel.Offset(0, -1).Value = cel.Offset(0, -2).Value + 21
End If

If InStr(1, cel.Value, "C") > 0 Then
cel.Offset(0, -1).Value = cel.Offset(0, -2).Value + 30
End If



Next cel


With ActiveSheet.Sort


.SortFields.Clear

.SortFields.Add Key:=Range("E2"), Order:=xlAscending
.SortFields.Add Key:=Range("F2"), Order:=xlAscending
.SortFields.Add Key:=Range("D2"), Order:=xlDescending


.SetRange Range("B2:H199")
.Header = xlYes
.Apply

End With


Dim rCheck As Range
Dim rHide As Range
Dim rCheckCell As Range

Set rCheck = ActiveWorkbook.ActiveSheet.Range("B3:B599")
rCheck.EntireRow.Hidden = False

For Each rCheckCell In rCheck.Cells
If InStr(1, rCheckCell, "Closed", vbTextCompare) > 0 Then
If Not rHide Is Nothing Then Set rHide = Union(rHide, rCheckCell) Else Set rHide = rCheckCell
End If
Next rCheckCell

If Not rHide Is Nothing Then rHide.EntireRow.Hidden = True

End Sub

 

[00fil00]

Sorry, that code is pasted twice for some reason. Here it is again:

Sub SortMultipleColumns()

Dim SrchRng As Range, cel As Range


Set SrchRng = Range("F3:F599")



For Each cel In SrchRng

If InStr(1, cel.Value, "A") > 0 Then
cel.Offset(0, -1).Value = cel.Offset(0, -2).Value + 7
End If

If InStr(1, cel.Value, "B") > 0 Then
cel.Offset(0, -1).Value = cel.Offset(0, -2).Value + 21
End If

If InStr(1, cel.Value, "C") > 0 Then
cel.Offset(0, -1).Value = cel.Offset(0, -2).Value + 30
End If



Next cel


With ActiveSheet.Sort


.SortFields.Clear

.SortFields.Add Key:=Range("E2"), Order:=xlAscending
.SortFields.Add Key:=Range("F2"), Order:=xlAscending
.SortFields.Add Key:=Range("D2"), Order:=xlDescending


.SetRange Range("B2:H199")
.Header = xlYes
.Apply

End With


Dim rCheck As Range
Dim rHide As Range
Dim rCheckCell As Range

Set rCheck = ActiveWorkbook.ActiveSheet.Range("B3:B599")
rCheck.EntireRow.Hidden = False

For Each rCheckCell In rCheck.Cells
If InStr(1, rCheckCell, "Closed", vbTextCompare) > 0 Then
If Not rHide Is Nothing Then Set rHide = Union(rHide, rCheckCell) Else Set rHide = rCheckCell
End If
Next rCheckCell

If Not rHide Is Nothing Then rHide.EntireRow.Hidden = True

End Sub