Find Text in VBA if it is IS Nothing then...

[jkbourland]

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

 

[Tony James]

Q1 - yes, though I would dim dates as Date. Also with so many
variables it's best to put Option Explicit at the top of the code, to
ensure spelling errors are trapped.

Q2 - the logic in Find method is the wrong way round. It should be:

Set DtFindResult = .Find(Dt, LookIn:=xlValues)
If Not DtFindResult Is Nothing Then
'yes item found - place found it code here
Else
'place didn't find it code here
End If

Q3 - you could call a function or sub that returns a boolean result
(true/false) to do the searching.

------------------------------------
Also I would use Range Objects instead of the Selection Object.

Below is some code that replicates yours, but I've taken values from
named ranges on Sheet1 instead of text boxes on a userform and it uses
Range objects and the Find method logic is corrected. It isn't the
final answer to your problem but should get you a bit nearer to solving
it.

hth, Tony

-----------------------------

Option Explicit
Private Sub test()
Dim Amt As String 'dollar amount such as 45.02
Dim Clnt As String 'Client Number such as 47565
Dim Dt As Date 'Date of deposit such as 8/22/2006
Dim rAmt As String 'Row number of the dollar amount
Dim cAmt As String
Dim amtCell As String
Dim txtAmtRow As String
Dim AmtInputRng As String
Dim cClnt As String 'Column number of Client number
Dim dateSearch As Range
Dim amountSearch As Range
Dim clientSearch As Range
Dim rcdt As String
Dim MyFormula As String

Dim DtFindResult As Range
Dim AmtFindResult As Range
Dim ClntFindResult As Range

Amt = Sheets(1).Range("amount") 'txtAmount.Value
Clnt = Sheets(1).Range("client") 'txtClient.Value
Dt = Sheets(1).Range("date") 'txtDate.Value

On Error GoTo ErrorHandler

Set dateSearch = ActiveSheet.Range("a:a")
With dateSearch
Set DtFindResult = .Find(Dt, , LookIn:=xlValues)
If Not DtFindResult Is Nothing Then
rcdt = DtFindResult.Address
MsgBox "Date " & Dt & " exists - address " & rcdt

Else
MsgBox "Can't find the date entered, " & Dt & ". Please
try another date."
End If
End With

Set amountSearch = ActiveSheet.Range("b:b")

With amountSearch
Set AmtFindResult = .Find(Amt, LookIn:=xlValues)
If Not AmtFindResult Is Nothing Then
rAmt = AmtFindResult.Row
MsgBox "Amount " & Amt & " exists - row " & rAmt
amtCell = AmtFindResult.Address
Else
MsgBox "Can't find the amount entered, Please try
another amount."
End If
End With

Set clientSearch = ActiveSheet.Range("c:c")
With clientSearch
Set ClntFindResult = .Find(Clnt, LookIn:=xlValues)
If Not ClntFindResult Is Nothing Then
cClnt = Split(ClntFindResult.Address, "$")(1)
MsgBox "Client Number " & Clnt & " exists - column " &
cClnt
Else
MsgBox "Can't find the client number entered," & Clnt & ".
Please check to see that it exists."
End If
End With
AmtInputRng = cClnt & rAmt
Range(AmtInputRng).Formula = "=" & amtCell

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

 

[jkbourland]

Wow....thank you so much!! I was able to get it to work.

Q3: Due to being self taught, I'm going to have to study functions in
order to better understand them. But I was able to get it to work the
long way. =)

You are wonderful and I truly appreciate you taking the time to help me
figure this out! I couldn't have done it otherwise!

Thank you again for all your time and help.
Kerri