Simple Excel VBA search ...

[vodkasoda]

... 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 ...

 

[VoG]

Try

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]

Here is what I came up with:

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]

I started the search at A2 since the first search starts after the first cell.

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]

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]

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

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]

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 ...