"Find" Macro

[scottnshelly]

hello,
I am trying to make a macro that will be user-friendly. I want it t
pop up an input box and ask for an agent's ID number, then go to th
database page and do a search for the ID number entered into the inpu
box. i have tried for about 3 hours now to make this work with n
avail. any suggestions?

here is what i have been trying:

Private Sub CommandButton1_Click()
Dim varInput As Integer

varInput = InputBox("ID", "SEARCH FOR AGENT'S ID NUMBER")
Sheets("database").Range("iv1").Value = varInput


Sheets("DATABASE").Select
Cells.Find(What:=(here is my mail problem), After:=ActiveCell
LookIn:=xlFormulas, LookAt
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext
MatchCase:= _
False, SearchFormat:=False).Activate
Selection.Copy
end sub


Thank

 

[Charles]

Scott,

Wyh not use a form? instead of a input box.

Sheets("DATABASE").activate
with userform1
Cells.Find(What:=.tbnumber.Text, lookat:=xlPart).Select
Selection.Copy
end with


HTH

Charle

 

[scottnshelly]

Thanks,
It did give me an error though. The error message said "compile error
Method or data member not found"
when i hit OK it highlights ".tbnumber"
am i supposed to change that to something? any other suggestions?
Thanks again

 

[Tom Ogilvy]

Private Sub CommandButton1_Click()
Dim varInput As Long

var = InputBox("ID", "SEARCH FOR AGENT'S ID NUMBER")
if isnumeric(var) and var <> "" then
varInput = clng(varInput)
Else
exit sub
End Sub
Worksheets("database").Range("iv1").Value = varInput

With Worksheets("DATABASE")
.Activate

set rng = .Cells.Find(What:=VarInput, After:=.Range("IV65536"), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
End With
if not rng is nothing then
rng.copy
else
msgbox VarInput & " not found"
End if
end sub

 

[scottnshelly]

Thanks,
It did give me an error though. The error message said "compile error
Method or data member not found"
when i hit OK it highlights ".tbnumber"
am i supposed to change that to something? any other suggestions?
Thanks again

 

[scottnshelly]

You're a genius! I don't know how you do this. I don't know if yo
have a little genie named Kazoo, or what, but you must be magical.
Thanks a million.
congratulaton

 

[Charles]

Scott,

You can change the .tb? to textbox1.text. The example I sent was for
project i was working on.
Please note you need to create a userform for this code to work.
If you require more let me know.

Charle

 

[scottnshelly]

I got it to work, Thanks a lot Charles. I don't know how you come u
with this information, but it sure is helpful to me!
thanks again

 

[scottnshelly]

Charles, or anyone else,
I actually got an error message when i tried this code. I put it as
click event on a button and when i do, it highlights: ".textbox1"
any suggestions?
thank

 

[Charles]

Scott,

Did you create a userform with textbox1? You indicated once that i
work? It should still work. If not e-mail me what you have and I'l
take a look at it. Just click on my name to send an e-mail.

Charle

 

[scottnshelly]

I thought that it worked, but it didn't. I did create the userform wit
a textbox1 and a label1. I can't e-mail it though, because it i
classified company information. It highlights .textbox1 when i try it

 

[scottnshelly]

I got it working, but now i need something that says if there are n
matches then give me a box that says "no matches found" and go t
another pag

 

[scottnshelly]

anyone know how to add to this code to make it give me a box if ther
are no matches found that says "no matches found"?
also need to know how to copy the cell that matched and the four cell
to the right of it.
Thanks

 

[Dave Peterson]

Maybe:

Option Explicit
Private Sub CommandButton1_Click()

Dim varInput As String
Dim FoundCell As Range
Dim DestCell As Range

varInput = InputBox("ID", "SEARCH FOR AGENT'S ID NUMBER")

If IsNumeric(varInput) = False Then
MsgBox "Enter a number"
Exit Sub
Else
With Sheets("DATABASE").UsedRange
Set FoundCell = .Cells.Find(What:=varInput, _
After:=.Cells(.Cells.Count), LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
End With

If FoundCell Is Nothing Then
MsgBox "Not found!"
Exit Sub
Else
With Worksheets("othersheet")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
FoundCell.Resize(1, 5).Copy _
Destination:=DestCell
End If
End If

End Sub

 

[scottnshelly]

You're so awesome!
how the hell do you know all of this? will i ever be as smart as you?
one question, i want the first found match to go in range a5:e5 and th
next one to go in the next blank. i know how to do a loop, but how d
i start the loop at a5 in this code?
thanks a millio

 

[Charles]

Scott,

Here is a code that should work
not tested though

Sheets("DATABASE").Activate
With userform1
On Error Resume Next
Cells.Find(What:=textbox1.Text, lookat:=xlPart).Select
If Err = 91 Then
MsgBox "I'm Sorry could not find From Date: " & textbox1.Text
Else
Range(ActiveCell, ActiveCell.Offset(0, 4)).Copy
End With

Now you need to paste it where ??

HTH

Charle

 

[Dave Peterson]

It sounds like you're running the macro (multiple times) and just moving those
found cells into the next available row. And I'm still guessing that you're
putting the data into a different worksheet:

Option Explicit
Private Sub CommandButton1_Click()

Dim varInput As String
Dim FoundCell As Range
Dim DestCell As Range

varInput = InputBox("ID", "SEARCH FOR AGENT'S ID NUMBER")

If IsNumeric(varInput) = False Then
MsgBox "Enter a number"
Exit Sub
Else
With Sheets("DATABASE").UsedRange
Set FoundCell = .Cells.Find(What:=varInput, _
After:=.Cells(.Cells.Count), LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
End With

If FoundCell Is Nothing Then
MsgBox "Not found!"
Exit Sub
Else
With Worksheets("othersheet")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
If DestCell.Row < 5 Then
Set DestCell = .Range("a5")
End If
End With
FoundCell.Resize(1, 5).Copy _
Destination:=DestCell
End If
End If

End Sub


And one thing that scares me:
With Sheets("DATABASE").UsedRange
I would think that you might want to be more specific--maybe pick the column (if
there's only one) that holds the ID's???

With sheets("database").Range("b:b")

 

[stevem]

Attempted this with what I'm doing (since I'm looking for the sam
thing)

I got an error of End With without With when attempting to run this

 

[Charles]

Stevem,

sorry i left out end if. see code below.

Sheets("DATABASE").Activate
With userform1
On Error Resume Next
Cells.Find(What:=textbox1.Text, lookat:=xlPart).Select
If Err = 91 Then
MsgBox "I'm Sorry could not find From Date: " & textbox1.Text
Else
Range(ActiveCell, ActiveCell.Offset(0, 4)).Copy
end if''''''''''''''''''''''add this
End With


Charle

 

[scottnshelly]

thanks a lot charles. i appreciate your being patient with m
ignorance. i wish some day to be half as smart as you with excel.
one more question on this particular code. when i run it, it gives m
an error. Compile error: variable not defined. then it highlight
textbox1
i checked the userform and it does indeed have a textbox1.
is there anything else that i need to do?
thanks agai