Hi,

I am using Excel 2003, this is my first Excel UserForm. I typically
create mine in Word. I have a UserForm that requires three values to
be filled in. I then want it to use the find feature and search for
the value in the document. When it finds it I want to activate the
cell and retrieve the Row or column letter/number to use later on. The
problem I am having is with the Find feature. I have searched through
the newsgroups and have gotten this far. I left some commented out
code so you can see what I've tried thus far. (Note: I am self
taught)

First Question: Did I Dim the values correctly. I used String even
though the value could be a number. And I saw one help that the Dim'ing
made a difference. The data in excel was imported over from Omega, a
larger database. Because of that I know that the dates don't always
come over as dates....they come over as text.

Second Question: Find doesn't always find the value like (95.00) even
though I can plainly see it in the document. Is this an excel glitch
or can I do something in my search to make it work better. (This
example was not in VBA just a regular Find)

Third Question: Find the **** to see the specific code I am referring
to....How can I change the Find so that I stops and goes back to the
form for the user to change the information if it couldn't find it???
My code will run if the value is valid or not. Perhaps I'm not
putting the Msgbox "can't find" in the right place.

Any help would be appreciated.

Thank you for your time....hopefully my code won't make your head spin.
Kerri


'Start of Code +++++++++++

Private Sub cmdOK_Click()
Dim Amt As String 'dollar amount such as 45.02
Dim Clnt As String 'Client Number such as 47565
Dim Dt As String 'Date of deposit such as 8/22/2006
Dim rAmt As String 'Row number of the dollar amount
Dim cClnt As String 'Column number of Client number

Amt = txtAmount.Value 'Text typed in the UserForm for Amound
Clnt = txtClient.Value
Dt = txtDate.Value
'rAmt = txtAmtRow.Value 'I have a txtbox to type the Row Value if the
Find can't find it.
'cClnt = txtCltCol.Text

On Error GoTo ErrorHandler

'go to top of doc - So the Find would start from there down.
Range("a1").Select

MyWkSht = ActiveSheet.Name
'MsgBox MyWkSht

MyRng = "E1:E3" '

'****
'Find the dt in column A
With Selection 'Used selection because I didn't get error.
''With Worksheets(MyWkSht).Range(MyRng) 'Tried this method from
help...didn't help
Set DtFindResult = .Find(Dt, LookIn:=xlValues)
If Not DtFindResult Is Nothing Then
MsgBox "Can't find the date entered, Please try another date."
'Need to stop and allow them to reinput correct date.
End
Else
MsgBox "Date Does Exists"
Cells.Find(Dt, LookIn:=xlValues).Activate '*****This is what
activates the cell****
rcdt = ActiveCell.Address
End If
End With

With Selection 'ActiveSheet.Range("E:E")
Set AmtFindResult = .Find(Amt, LookIn:=xlValues)
If Not AmtFindResult Is Nothing Then
MsgBox "Can't find the amount entered, Please try another
amount."
rAmt = txtAmtRow
Else
MsgBox "Amount Does Exists"
Cells.Find(Amt, LookIn:=xlValues).Activate
rAmt = ActiveCell.Row
cAmt = Split(ActiveCell.Address, "$")(1)
amtCell = cAmt & rAmt 'Cell Range for the Amount
'If it can't find the amount go to the row
End If
End With
''''''' DtFindReslut = Cells.Find(What:=Dt, After:=ActiveCell,
LookIn:=xlFormulas, _
''''''' lookat:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
''''''' MatchCase:=False, SearchFormat:=False) '.Activate
'''''''
''''''' If DtFindReslut = False Then
''''''' MsgBox "Couldn't Find the Date, Please recheck"

'Find the Amount from there down in column E 'Store that row number
as r integer
''''''' Else:
'.Activate
''''''' AmtFindResult = Cells.Find(What:=Amt, After:=ActiveCell,
LookIn:=xlFormulas, _
''''''' lookat:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
''''''' MatchCase:=False, SearchFormat:=False).Activate


''''''' If AmtFindResult = False Then
''''''' MsgBox "Couldn't Find the Amount typed, Please recheck."
& vbCr & _
''''''' "If you get this message again, Close and locate
the number yourself." & vbCr & _
''''''' "Then redisplay this box and type the row number
in the box to the right of Amount."

''''''' rAmt = txtAmtRow
''''''' Else:
''''''' rAmt = ActiveCell.Row
''''''' cAmt = Split(ActiveCell.Address, "$")(1)
''''''' amtCell = cAmt & rAmt 'Cell Range for the Amount
''''''' 'If it can't find the amount go to the row
'''''''
''''''' End If
''''''' End If

'Find the Client Number in row 3 'Store that column number as c
integer
'''' Range("3:3").Select 'go back to top for search
' ActiveCell.Rows.Select
Range("A1").Select
With Selection 'ActiveSheet.Range("E:E")
Set ClntFindResult = .Find(Clnt, LookIn:=xlValues)
If Not ClntFindResult Is Nothing Then
MsgBox "Can't find the client number entered, Please check to
see that it exists."
Else
MsgBox "Client Number Does Exists"
Cells.Find(Clnt, LookIn:=xlValues).Activate
cClnt = Split(ActiveCell.Address, "$")(1) 'So it pull the
current column letter not number
End If
End With

'This is how I started but It gave error immediately if it wasn't
there.
''''''' ClntFindResult = Selection.Find(What:=Clnt,
After:=ActiveCell, LookIn:=xlFormulas, _
''''''' lookat:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
''''''' MatchCase:=False, SearchFormat:=False).Activate
''''''' If ClntFindResult = False Then
''''''' MsgBox "Couldn't find the client number, please press
cancel and create."
''''''' Else:
''''''' cClnt = Split(ActiveCell.Address, "$")(1) 'So it pull the
current column letter not number in
''''''' End If
'rAmt & cClnt = AmtInputRng ' This will have the C456 which is the
location of the cell
AmtInputRng = cClnt & rAmt
Range(AmtInputRng).Select
MyFormula = "=" & amtCell
ActiveCell.Formula = MyFormula

'Go to AmtInputRng range do this formula = Amt 'Cell range column E &
rAmt

Me.Hide

Exit Sub
''ErrorHandler:
''MsgBox "There has been an error: " & Error() & Chr(13) _
'' & "Ending Sub.......Try again or check values typed in box.", 48
''
''Resume Next
End Sub