Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

inputbox macro help

  Asked By: John    Date: Oct 19    Category: MS Office    Views: 1104
  

After the user enters a name via the inputbox, I want to activate the
cell where is finds the result. Here is my code that does not work.
Any help would be appreciated. Thank you.

Sub FindName()
Dim Found = Cells.Find (What:=LookFor)
Dim ws As Worksheet, LookFor As Variant
LookFor = InputBox("Enter name to find")
If LookFor = "" Then Exit Sub
For Each ws In ActiveWorkbook.Worksheets
Set Found = ws.Cells.Find(what:=LookFor)
If Not Found Is Nothing Then Exit For
Next ws
If Found Is Nothing Then
MsgBox LookFor & " not found.", vbExclamation
Else
Cells.FindNext(After:=ActiveCell).Activate

Else
Application.Goto reference:=Found, scroll:=True
End If
End Sub

Share: 

 

5 Answers Found

 
Answer #1    Answered By: Josephine Gomez     Answered On: Oct 19

Please put Option Explicit at the top of (all) your module.

This would stop you from using LookFor (in the second line) before you
create it (in the third line). It won't catch the fact that you don't put
anything in it until the fourth line, though.

I haven't looked at your code  any closer than that. There could well be
other problems in it.

 
Answer #2    Answered By: Aadi Martin     Answered On: Oct 19

Maybe this could help. This would only look for the last instance of the
string you are looking for amongst the sheets. You can easily modify to
run thru all instances of search string to get the range required.

Option Explicit

Sub FindName()
Dim found  As Range
Dim ws As Worksheet, LookFor As String
LookFor = InputBox("Enter name to find")
If LookFor = "" Then Exit Sub
For Each ws In ActiveWorkbook.Worksheets
Set Found = Cells.Find(what:=LookFor, LookIn:=xlValues)
If Found Is Nothing Then Exit For
Next ws

If Found Is Nothing Then
MsgBox LookFor & " not found.", vbExclamation
Else
Found.Activate
End If

End Sub

 
Answer #3    Answered By: Jawwad Akram     Answered On: Oct 19

Thank you for taking a look at the code. Your help  was appreciated.

 
Answer #4    Answered By: Shirley Allen     Answered On: Oct 19

It looks like you've combined techniques from more than one source and haven't
cleaned it up.
Let's take a look at what you're doing:
Sub FindName()
Dim found  = Cells.Find (What:=LookFor)

Why are you DECLARING "Found" to be the result  of the find  method, then later
doing it again? Also, at this point, "LookFor" has no value.
I suggest changing the line to: Dim Found


Dim ws As Worksheet, LookFor As Variant 'Declaring Variables
LookFor = InputBox("Enter name to find") 'Prompting user  for name
If LookFor = "" Then exit  Sub ' If no name is supplied, exit
sub.
For Each ws In ActiveWorkbook.Worksheets 'Loop through all worksheets in
workbook
Set Found = ws.Cells.Find( what:=LookFor) 'Look for string on worksheet
If Not Found Is Nothing Then Exit For 'If string is found, stop
looking
Next ws '(end of worksheet
loop)
If Found Is Nothing Then ' If string is not found,
then issue message
MsgBox LookFor & " not found.", vbExclamation
Now, the next few lines get confusing.
You have TWO "else" statements, which should have caused a compiler error.
The findNext method is confusing here because you've gone to all the trouble to
find
the FIRST ocurrence of the string, then you're saying, "ok, skip that one and
take me to
the NEXT ocurrence". I suspect that these next two lines need to me removed.
Else
Cells.FindNext( After:=ActiveCell).Activate

Else
Application.Goto reference:=Found, scroll:=True 'This is the line that will
take you to the cell.
End If
End Sub

Now, if we clean up the code  you end up with:
'----------------------------------------------------------------
Option Explicit
Sub FindName()
Dim Found, ws As Worksheet, LookFor As Variant
LookFor = InputBox("Enter name to find")
If LookFor = "" Then Exit Sub
For Each ws In ActiveWorkbook.Worksheets
Set Found = ws.Cells.Find(What:=LookFor)
If Not Found Is Nothing Then Exit For
Next ws
If Found Is Nothing Then
MsgBox LookFor & " not found.", vbExclamation
Else
Application.Goto reference:=Found, Scroll:=True
End If
End Sub
'----------------------------------------------------------------
which seems to work.
Hope this helps.

 
Answer #5    Answered By: Myrna Brown     Answered On: Oct 19

It worked
perfectly. Thanks for your time on this.

 
Didn't find what you were looking for? Find more on inputbox macro help Or get search suggestion and latest updates.




Tagged: