Simple Excel VBA search ...

vodkasoda

Board Regular
Joined
Aug 12, 2004
Messages
86
... apologies for asking for something so simple, but you know when you have one of those days ? I'm having one of those and the kids are driving me mad so I can't think straight !!!

I want to simply :

Search column A (starting in A3) for a value that is held in a String variable (MyTeam)

Select that Cell if a match is found

Select the first empty Cell in the Column if no match is found

... see what I mean ?!?

Thanks in advance ...
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Sub srch()
Dim MyTeam As String, Found As Range, LR As Long
MyTeam = "Fred"
LR = Range("A" & Rows.Count).End(xlUp).Row
Set Found = Range("A3:A" & LR).Find(what:=MyTeam)
If Found Is Nothing Then
    Range("A" & LR + 1).Select
Else
    Found.Select
End If
End Sub

Edit: you should not need to Select that cell to work with it!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
Here is what I came up with:
Code:
Sub MySearch()

    Dim myTeam As String
    myTeam = "Test"

    On Error GoTo Find_Empty
    Range("A3:A" & Range("A65536").End(xlUp).Row).Select
    Selection.Find(What:=myTeam, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Select

    Exit Sub
    
Find_Empty:
    If Err.Number = 91 Then
        Range("A3").End(xlDown).Offset(1, 0).Select
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If

End Sub
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,121
Office Version
  1. 365
Platform
  1. Windows
I started the search at A2 since the first search starts after the first cell.

Code:
Sub FindMyTeam()
  Dim MyTeam As String, cell As Range
  MyTeam = "Cowboys"
  Set cell = Range("A2", Cells(Rows.Count, 1).End(xlUp).Address).Find(MyTeam, Range("A2"), xlValues)
  If cell Is Nothing Then
    Set cell = Range("A2:A" & Cells.Rows.Count).Find("", Range("A2"), xlValues)
    cell.Select
    Else
      cell.Select
  End If
End Sub
 

vodkasoda

Board Regular
Joined
Aug 12, 2004
Messages
86

ADVERTISEMENT

Many thanks to all 3 of you, I couldn't get my head around it, yet I knew it was so simple ... and yet every search I did just seemed to bring me to a solution for finding the last empty Cell in a Row ... and some of them were so over-complicated and long-winded they were not helping my state of mind at all !!!!

Thanks again ...
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Glad whatever you tried worked. However please bear in mind two things:

1. If you use the .Find method (which I recommend since it will be faster than looping) then you will see that my offering just specified the bare minimum what:= whereas Joe4's specified other parameters. If you use mine Excel will substitute what you used last in a search (or I guess the default settings).

2. Once you have found the cell you should not need to Select it. For example

Code:
Sub srch()
Dim MyTeam As String, Found As Range, LR As Long
MyTeam = "Fred"
LR = Range("A" & Rows.Count).End(xlUp).Row
Set Found = Range("A3:A" & LR).Find(What:=MyTeam)
If Found Is Nothing Then
Set Found = Range("A" & LR + 1)
End If
Found.Interior.ColorIndex = 3
End Sub
 

vodkasoda

Board Regular
Joined
Aug 12, 2004
Messages
86
Thank you Vog for your extra help ... I'll try to learn & remember this, but I can't promise !!!

However, in this instance, it is pretty much a once-off program that is collating a lot of data from a lot of different .TXT files in order for me to produce some statistical evidence, I think I've achieved that.

BUT ... if it needs re-visiting & may be used again, I will certainly bear this in mind, thanks again ...
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,316
Messages
5,600,910
Members
414,415
Latest member
joshuaba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top