Find or Search Problem

  • Thread starter Thread starter VBAvirgin
  • Start date Start date
V

VBAvirgin

Greetings,
I'm obviously new at VBA but I am attempting to understand it all.
There are a few of us where I work that have been given Excel to
replace our old program.
I am trying to put together a macro to prompt other users for input and
have their choice either accented or have that location jumped to or
indicated in some way.

My code is:

Sub FindIt()
Application.Goto Reference:="MyList"
Cells.Find(What:="*", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _

xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate

For some reason this code does not work.
Can anyone help me please?
 
If there's only one column in the list, you can use the worksheet function
=match() to see if it's there:

Option Explicit
Sub FindIt()

Dim myListRng As Range
Dim res As Variant
Dim myStr As String

myStr = InputBox(Prompt:="What one?")

If Trim(myStr) = "" Then
Exit Sub
End If

'only one column in the list?
Set myListRng = Worksheets("sheet1").Range("mylist")

res = Application.Match(myStr, myListRng, 0)

If IsError(res) Then
MsgBox myStr & " wasn't found!"
Else
Application.Goto myListRng(res), scroll:=True
End If

End Sub

Is your "myList" range more than one column?

If yes, then maybe something like this will work ok:

Option Explicit
Sub FindIt()

Dim myListRng As Range
Dim FoundCell As Range
Dim myStr As String

myStr = InputBox(Prompt:="What one?")

If Trim(myStr) = "" Then
Exit Sub
End If

'only one column in the list?
Set myListRng = Worksheets("sheet1").Range("mylist")

With myListRng
Set FoundCell = .Cells.Find(what:=myStr, _
after:=.Cells(.Cells.Count), _
lookat:=xlPart, LookIn:=xlValues, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox myStr & " wasn't found!"
Else
Application.Goto FoundCell, scroll:=True
End If

End Sub
 
Back
Top