Hello,
I am pretty new to VBA and I am currently trying to make a macro that applies a filter to a certain column based on the text a user enters into an input box. My worksheet has columns with months as headers from cell B2:Z2 in the format mmm'yy (i.e. Nov'15) and data below each month. I want the macro to allow the user to enter a month and year in the same format into an input box then for excel to find and select the column containing that month and year and apply a filter to the data.
This is the code that I currently have but it is giving me an error:
Sub Test()
Dim iPutFound As Range
Dim iPut As String
iPut = Application.InputBox("Select Month", Type:=2)
Set iPutFound = Sheets("Sheet1").Range("B1:Z1").Find(What:=iPut, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not iPutFound Is Nothing Then
ActiveSheet.Range("$A$1:$Z$9359").AutoFilter Field:=iPutFound, Criteria1:="1"
Else
MsgBox "No match found."
End If
End Sub
Thank you in advance!
I am pretty new to VBA and I am currently trying to make a macro that applies a filter to a certain column based on the text a user enters into an input box. My worksheet has columns with months as headers from cell B2:Z2 in the format mmm'yy (i.e. Nov'15) and data below each month. I want the macro to allow the user to enter a month and year in the same format into an input box then for excel to find and select the column containing that month and year and apply a filter to the data.
This is the code that I currently have but it is giving me an error:
Sub Test()
Dim iPutFound As Range
Dim iPut As String
iPut = Application.InputBox("Select Month", Type:=2)
Set iPutFound = Sheets("Sheet1").Range("B1:Z1").Find(What:=iPut, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not iPutFound Is Nothing Then
ActiveSheet.Range("$A$1:$Z$9359").AutoFilter Field:=iPutFound, Criteria1:="1"
Else
MsgBox "No match found."
End If
End Sub
Thank you in advance!