4. VBA Tips - Finding things using VBA
Don't use VLOOKUP, INDEX, or MATCH from VBA when finding things on a worksheet with VBA.
- The problem with these is that when the function doesn't find something, it will return a VBA error which you then have to trap and handle successfully.
- This is a very messy way of coding, and prone to errors.
The best way to look for things on a sheet is to use .Find
Here's an example that finds all whole instances of "Nick" on this sheet:
This code will find all instances of "Nick":
Download workbook with code on how to find all instances of a string on a worksheet
Training Video on how to use FIND in VBA:
»
- Nick's blog
- Login or register to post comments
- 30551 reads
Let User To Insert word to search
[CODE]
Sub FindNick()
strName = InputBox(Prompt:="You wordItem please.", Title:="ENTER TO SEARCH")
If strName <> "" Then
With ActiveSheet.Cells
thingToLookFor = strName
Set FoundIt = .Find(thingToLookFor, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False)
If Not FoundIt Is Nothing Then
FirstAddress = FoundIt.Address
Do
FoundIt.Select
MsgBox "Found " & thingToLookFor & " in cell" & FoundIt.Address
Set FoundIt = .FindNext(FoundIt)
Loop While Not FoundIt Is Nothing And FoundIt.Address <> FirstAddress
Else
MsgBox "didn't find it !"
End If
End With
Else
MsgBox "Please Insert at least a word to search"
End If
End Sub
[/CODE]
Modified Code
Okay so working off this code there are two things I want to do but cannot really figure out how.
First consider the code I have below:
Sub FindNick()
With ActiveSheet.Cells
thingToLookFor = "Nick"
Set FoundIt = .Find(thingToLookFor, LookIn:=xlValues, LookAt:=xlWhole)
If Not FoundIt Is Nothing Then
FirstAddress = FoundIt.Address
Do
FoundIt.Offset(X, Y).Select
Selection.Copy
Range("D1").Select
ActiveSheet.Paste
Set FoundIt = .FindNext(FoundIt)
Loop While Not FoundIt Is Nothing And FoundIt.Address <> FirstAddress
Else
MsgBox "didn't find it !"
End If
End With
End Sub
Sorry about the formatting but basically instead of displaying a box I want to have it take the cell to the right of the thing it found and paste it into another cell (D1) in this case. Now with my formula this works but I want it to continue searching for further instances of "Nick" and post the values next to it in "D2" "D3" and so on for each instance....is this possible?
Also is it possible to make it search for two words in the same macro. Basically in this scenario search for "Nick" and then when finished with "Nick" search for each instance of "Don"?
Please help!