Simple search macro

[Rob_010101]

Hello

In C1 i'd like to be able to type a name and when the search button is clicked, excel performs the search function.

I've tried recording a macro for this but it keeps going to an incorrect cell.

Is someone able to help with VB that I can assign to the button?

Many Thanks

 

[dreid1011]

Here is some code I wrote in 2015 for a search. The true condition for the IF is if the search fails, so change the those lines to whatever you want it to do if the name is not found.

Private Sub SearchButton_Click()
    Application.EnableEvents = False
  
    If IsError(Application.Match(Range("C1").Value, ws.Range("B5:B100"), 0)) Then
            MsgBox "Account does not exist."
        Else
            ws.Range("B" & trgtRow).Select
    End If
    Application.EnableEvents = True
End Sub

 

[Rob_010101]

This won't work I'm afraid.

I'm getting Runtime error 424 object required

 

[dreid1011]

There is a worksheet variable in there, and it looks like I removed some of the other code, so if you use it as is, that error is not surprising. You'll have to declare the worksheet variable and set it to your sheet. In the code below, you would replace " your sheet name here " with the name of your worksheet, minus the extra spaces, but keep the quotation marks. Also, in my sheet the target value was in column B, so make sure you change the ranges to suit your data.

Private Sub SearchButton_Click()
Dim ws As Worksheet
Set ws = Sheets(" your sheet name here ")
    Application.EnableEvents = False
 
    If IsError(Application.Match(Range("C1").Value, ws.Range("B5:B100"), 0)) Then
            MsgBox "Account does not exist."
        Else
            ws.Range("B" & trgtRow).Select
    End If
    Application.EnableEvents = True
End Sub

 

[Rob_010101]

There is a worksheet variable in there, and it looks like I removed some of the other code, so if you use it as is, that error is not surprising. You'll have to declare the worksheet variable and set it to your sheet. In the code below, you would replace " your sheet name here " with the name of your worksheet, minus the extra spaces, but keep the quotation marks. Also, in my sheet the target value was in column B, so make sure you change the ranges to suit your data.

Private Sub SearchButton_Click()
Dim ws As Worksheet
Set ws = Sheets(" your sheet name here ")
    Application.EnableEvents = False
 
    If IsError(Application.Match(Range("C1").Value, ws.Range("B5:B100"), 0)) Then
            MsgBox "Account does not exist."
        Else
            ws.Range("B" & trgtRow).Select
    End If
    Application.EnableEvents = True
End Sub

Hello,

I'm now getting this:

 

[dreid1011]

Ah, another variable, my apologies.

Private Sub SearchButton_Click()
Dim ws As Worksheet
Dim trgtRow As Long
Set ws = Sheets("2024")
    Application.EnableEvents = False
 
    If IsError(Application.Match(Range("C1").Value, ws.Range("A1:A100"), 0)) Then
            MsgBox "Account does not exist."
        Else
            trgtRow = Application.Match(Range("C1").Value, ws.Range("A1:A100"), 0))
            ws.Range("A" & trgtRow).Select
    End If
    Application.EnableEvents = True
End Sub

 

[Rob_010101]

No need to apologise, I'm grateful for your help!

Edit: Sorted it by adding the missing opening parenthesis before "Application"

Thanks for your help! Much appreciated

 

[dreid1011]

Yes, well, I should know better Try this. There was an extra ")" on that line.

Private Sub SearchButton_Click()
Dim ws As Worksheet
Dim trgtRow As Long
Set ws = Sheets("2024")
    Application.EnableEvents = False
 
    If IsError(Application.Match(ws.Range("C1").Value, ws.Range("A1:A100"), 0)) Then
            MsgBox "Account does not exist."
        Else
            trgtRow = Application.Match(ws.Range("C1").Value, ws.Range("A1:A100"), 0)
            ws.Range("A" & trgtRow).Select
    End If
    Application.EnableEvents = True
End Sub

 

[Rob_010101]

Yes, well, I should know better Try this. There was an extra ")" on that line.

Private Sub SearchButton_Click()
Dim ws As Worksheet
Dim trgtRow As Long
Set ws = Sheets("2024")
    Application.EnableEvents = False
 
    If IsError(Application.Match(ws.Range("C1").Value, ws.Range("A1:A100"), 0)) Then
            MsgBox "Account does not exist."
        Else
            trgtRow = Application.Match(ws.Range("C1").Value, ws.Range("A1:A100"), 0)
            ws.Range("A" & trgtRow).Select
    End If
    Application.EnableEvents = True
End Sub

Thanks for your help! Much appreciated

 

[dreid1011]

You're welcome, and happy to help.