Bookmark page
Printable version
Course updatesForum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel VBA Find Method (CTRL + F)
|
|
|
Bookmark this page|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel VBA Find Method (CTRL + F)
Resolved · High Priority · Version 2003
Reply
Excel VBA Find Method (CTRL + F)
Hello,After my Excel VBA course I have taken to writing code very well but one area I am having extreme trouble with is FIND functionality.
I wish to have a subroutine for finding a value within a Range (B:B) in a spreadsheet. The value would come from a text box in a user form so assume that this will be a variable.
If the item is matched then it is to show a message box and end.
If there is no match then it needs to go onto a seperate subroutine.
Any help would be much appreciated.
Thanks
Brian
For upcoming training course dates see: Pricing & availability
RE: Excel VBA Find Method (CTRL + F)
Hi BrianThanks for your question
It seems that the find tool is not supported in VBA. However it is easy to do what you say with a simple looping and decision structure. The following seems to work well
Sub Find()
Dim MyRange As Range
Dim i As Integer
Set MyRange = Columns("A:A")
For i = 1 To MyRange.CurrentRegion.Rows.Count
If MyRange.Cells(i, 1).Value = "Peter" Then
MsgBox "Found it"
Exit Sub
End If
Next i
Call Subroutine
End Sub This simply cycles through each cell in the column looking for a value. If it is found it displays a message box and ends the routine. If it gets to the end with no result it simply calls the sub procedure. I have entered the value as a literal, but this is easily replaced with a variable holding the text box's value.
Hope this helps
Regards
Stephen
|
 |
|
» Forum post: Multimedia - adding video files |
How do I play a video files from my presentation?
