Using VBA to search a range, and return a result
Posted by Daniel van Vliet on November 26, 2001 10:14 PM
Hi,
What I am trying to acheive is attaching a code to a command button, such that when press it will request the user to find a value, the user enters the vale, press's ok, excel will locate the value, and return/action another cell.
Basically I want to search a range for specfic value, using an input box, and then preform an action
This is what I've got so far:
Range("a1:d10").Value = FindText = InputBox("Search for: ")
The above will give me the inputbox asking to search, butI can't get it to actually find the value in the range and action it.
Tks in advance for your help.
Rgds,
Daniel van Vliet.
Posted by Steven on November 26, 2001 10:33 PM
Hi Daniel,
This code might help you do the trick.
Sub myFind()
Range("A1:D10").Select
myValue = InputBox("Please enter value")
Selection.Find(What:=myValue, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Activate
End Sub
Regards
Steven
Posted by Joe Was on November 27, 2001 6:27 AM
Sub Macro2()
'This will search your sheet for any cell containing a portion of your input.
'You can continue your search from the last find and the location of the find is activated on the sheet.
'This was designed to work as a Form Button or a Hot-Key.
'By: Joe Was, 11/2001
Dim mySearch As String
Dim myData As String
Dim Searchl As Integer
Dim Datal As Integer
Dim PofText
Dim LofText
'Get user search info.
mySearch = Application.InputBox("Enter your search text:", Title:="Fuzzy Match, from the Active Cell", Type:=2)
'Search Sheet, from current cursor location out as case sensitive and as part of a cell entry.
Cells.Find(What:=mySearch, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True).Activate
'Locate cell with match.
Cells.FindNext(After:=ActiveCell).Activate
'Count find lengths and calculate percent.
myData = ActiveCell
Searchl = Len(mySearch)
Datal = Len(myData)
If Datal <= Searchl Then
LofText = (Datal / Searchl)
Else
LofText = (Searchl / Datal)
End If
PofText = FormatPercent(LofText)
'Display found data and display percent of match.
MsgBox "A Match is: '" & myData & "', a fuzzy match of: " & PofText & "."
End Sub
You can change this to not do a "Fuzzy" search by changing "xlPart" to "xlWhole." JSW
