Simple Request

00fil00

New Member
Joined
Jul 15, 2022
Messages
4
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

00fil00

New Member
Joined
Jul 15, 2022
Messages
4
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
 

Forum statistics

Threads
1,176,238
Messages
5,902,090
Members
434,939
Latest member
MrDrock

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top