Excel VBA Find Method (CTRL + F)

Resolved · High Priority · Version 2003

Wed 19 May 2010, 16:38 Delegate Brian said...

Brian has attended:
Excel VBA Intro Intermediate course
Excel VBA Advanced course

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

Fri 21 May 2010, 09:35 Edited on Fri 21 May 2010, 09:35 Trainer Stephen said...

RE: Excel VBA Find Method (CTRL + F)

Hi Brian

Thanks 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

Training courses