VBA requirement for searching string that begines with...

[omairhe]

Hey all,

In need of a simple code that will search string in Column A that begins with "BEL" . Once searched goto target.row.

Will appreciate any help.

Thank you.

 

[mikerickson]

Try this

With ActiveSheet.Range("A:A")
    .Find(What:="BEL*", After:=ActiveCell.EntireRow.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, _
        SearchDirection:=xlNext, MatchCase:=False).Activate
End With

This is case insensitive.

 

[Fluff]

How about

Sub omairhe()
   Dim Fnd As Range
   Set Fnd = Range("A:A").Find("BEL*", , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then Application.Goto Fnd, True
End Sub

 

[omairhe]

How about

Sub omairhe()
   Dim Fnd As Range
   Set Fnd = Range("A:A").Find("BEL*", , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then Application.Goto Fnd, True
End Sub

Yes. What might change in your code for keeping the same column reference used before triggering the code.
Right now it will select target row and column A . whereas I would like it to select target row and keep column reference as before.

Thank you for your effort..

 

[omairhe]

Thanks Mike, I noticed your code just now. I ran it and it is working gr8. Just want the column reference to be the same as it was before running the code.

Try this

With ActiveSheet.Range("A:A")
    .Find(What:="BEL*", After:=ActiveCell.EntireRow.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, _
        SearchDirection:=xlNext, MatchCase:=False).Activate
End With

This is case insensitive.

 

[Fluff]

How about

Sub omairhe()
   Dim Fnd As Range
   Set Fnd = Range("A:A").Find("BEL*", , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then ActiveWindow.ScrollRow = Fnd.Row
End Sub

 

[omairhe]

How about

Sub omairhe()
   Dim Fnd As Range
   Set Fnd = Range("A:A").Find("BEL*", , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then ActiveWindow.ScrollRow = Fnd.Row
End Sub

Almost. It is now keeping the column as before but not selecting the target row.

whereas I would like it to select target row and keep column reference as before

 

[Fluff]

Ok, how about

Sub omairhe()
   Dim Fnd As Range
   Set Fnd = Range("A:A").Find("BEL*", , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then Cells(Fnd.Row, ActiveCell.column).Activate
End Sub

 

[omairhe]

Ok, how about

Sub omairhe()
   Dim Fnd As Range
   Set Fnd = Range("A:A").Find("BEL*", , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then Cells(Fnd.Row, ActiveCell.column).Activate
End Sub

Awesome. Thanks a lot.

 

[Fluff]

You're welcome & thanks for the feedback