VBA to read in user input and search a worksheet based on input

[SkylarP]

I have a button that when pressed uses an InputBox to ask for a date. The user enters a date and then VBA should search the worksheet for the date the user entered and return the entire row(s) that had the date specified. So far, I have the input box, a little validation, a match function and a loop. I get an error on "info!" in the line (If WorksheetFunction.CountIf(info!, usrInput) = 0) that says Type mismatch. This is where I get stuck, I am currently googling but thought I would post here as well. if this code will do what I need it to once the type error gets sorted out, how can I return the entire row(s) if it has the date searched for?

Sub Search()
usrInput = InputBox("Please enter a date", "search", "enter date as DD/MM/YYYY")
If usrInput = "" Then Exit Sub

If WorksheetFunction.CountIf(info!, usrInput) = 0 Then
MsgBox "Not found"
Else

rr = WorksheetFunction.Match(usrInput, info!, 0)
For c = 1 To 45 'range of columns to search'
Data = Data & Cells(rr, c) & "-"
Next c
MsgBox (Data)
End If

End Sub

 

[offthelip]

your worksheet function need a range as input info! is not a range it is the name of a workhseet ( I presume) try this:

Sub Search()
Dim wr As Range

usrInput = InputBox("Please enter a date", "search", "enter date as DD/MM/YYYY")
If usrInput = "" Then Exit Sub
Set wr = Worksheets("info").UsedRange

If WorksheetFunction.CountIf(wr, usrInput) = 0 Then
MsgBox "Not found"
Else

rr = WorksheetFunction.Match(usrInput, wr, 0)
For c = 1 To 45 'range of columns to search'
Data = Data & Cells(rr, c) & "-"
Next c
MsgBox (Data)
End If

End Sub

 

[SkylarP]

your worksheet function need a range as input info! is not a range it is the name of a workhseet ( I presume) try this:

Sub Search()
Dim wr As Range

usrInput = InputBox("Please enter a date", "search", "enter date as DD/MM/YYYY")
If usrInput = "" Then Exit Sub
Set wr = Worksheets("info").UsedRange

If WorksheetFunction.CountIf(wr, usrInput) = 0 Then
MsgBox "Not found"
Else

rr = WorksheetFunction.Match(usrInput, wr, 0)
For c = 1 To 45 'range of columns to search'
Data = Data & Cells(rr, c) & "-"
Next c
MsgBox (Data)
End If

End Sub

Thank you for your input, I am now getting a run-time error '1004' Unable to get the Match Property of the worksheetfunction class. currently googling to see if I can solve myself but if see this before then any idea why the Match function isn't working?

 

[Marc L]

​

Never use WorksheetFunction except if you wanna see your VBA procedure crashing when the Excel function returns an error !​

​

Use Application rather than WorksheetFunction and check the Excel function result with VBA functions IsError or IsNumeric …​

​

As a reminder the Excel MATCH function can search only in a single column or row so use the VBA Range.Find method instead …​

 

[SkylarP]

I fixed my issue by the below code:

Dim key As Variant
Dim c As Range
Dim firstaddress As String
Dim n As Integer
n = 0

key = InputBox("Please enter a date", "Search")

With Worksheets("Data").Range("date_range")
Set c = .Find(key, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
c.EntireRow.Copy Sheets("Search").Range("A2").Offset(n, 0)
Set c = .FindNext(c)
n = n + 1
Loop While Not c Is Nothing
End If
End With