mcallaghan
New Member
- Joined
- May 3, 2013
- Messages
- 19
Hi Guys, macro novice struggling to adapt a macro in an excel doc I inherited. It filters one sheet based on the activecell in another. I'd like to change to filter by whether it CONTAINS the activecell, but when I put ** around activecell in the formula, it just looks for whether it contains the word activecell. Below is the macro I'm using, thanks in advance
Sub FilterPriortyActions()
Dim FilterCrit As String
If ActiveCell = "" Then
MsgBox "The Selected Cell is Blank! Please Select a Cell with a Valid ID from Column A"
Exit Sub
End If
If InRange(ActiveCell, Range("A4:A500")) Then
' code to handle that the active cell is within the right range
FilterCrit = ActiveCell
Sheet8.Cells(1, 2).Value = ActiveSheet.Name
Sheet7.Select
ActiveSheet.Unprotect
With Sheet7
.AutoFilterMode = False
.Range("F3:Y3").AutoFilter
.Range("F3:Y3").AutoFilter Field:=2, Criteria1:=FilterCrit
End With
Else
' code to handle that the active cell is not within the right range
MsgBox "Selected Cell is NOT in the Unique ID column! Please Select a Cell from Column A"
End If
End Sub
Sub FilterPriortyActions()
Dim FilterCrit As String
If ActiveCell = "" Then
MsgBox "The Selected Cell is Blank! Please Select a Cell with a Valid ID from Column A"
Exit Sub
End If
If InRange(ActiveCell, Range("A4:A500")) Then
' code to handle that the active cell is within the right range
FilterCrit = ActiveCell
Sheet8.Cells(1, 2).Value = ActiveSheet.Name
Sheet7.Select
ActiveSheet.Unprotect
With Sheet7
.AutoFilterMode = False
.Range("F3:Y3").AutoFilter
.Range("F3:Y3").AutoFilter Field:=2, Criteria1:=FilterCrit
End With
Else
' code to handle that the active cell is not within the right range
MsgBox "Selected Cell is NOT in the Unique ID column! Please Select a Cell from Column A"
End If
End Sub