Solved

Modifying VBA Search Code

Posted on 2011-03-21
2
351 Views
Last Modified: 2013-11-27
I have the following code in use on a userform within my workbook.

Basically it searches by a particular column for a numeric value and then pouplates the userform with the contents of that row.

Private Sub cmdSearch_Click()
   Dim rw As Long             'row the search value was found on
   Dim myVal As Long          'converted search value
 
   On Error Resume Next
      'validate user input
      If txtSearch.Value = "" Or _
         Not IsNumeric(CLng(txtSearch.Value)) Then
         MsgBox "Enter valid SIR number!"
         txtSearch.SetFocus
         Exit Sub
      End If
 
      'convert the TexBox value to  data type long and get row number
      myVal = CLng(txtSearch.Value)
      On Error Resume Next
 
       With Sheets("SIRs").Range("C:C")
         Set cell = .Find(myVal, LookIn:=xlValues)
         rw = cell.Row
       End With
   On Error GoTo 0

   On Error GoTo 0
   If rw = 0 Then
      MsgBox "SIR Not Found"
   Else
      Call PopulateTextBoxes(rw)
   End If
End Sub

Open in new window


I now want to create a new search function whereby it searches another column (E) but for a non-numeric value this time. Naively I assumed I could copy the above code, tailor it for column E and remove the following piece of code

Or _
         Not IsNumeric(CLng(txtSearch.Value)) 

Open in new window


Unfortunately it keeps returning a 'SIR not Found' result.

can anyone help please?
0
Comment
Question by:laffs_efc
2 Comments
 
LVL 7

Accepted Solution

by:
Pr1z earned 125 total points
ID: 35182763
Line 3 defines a data type of Long ==> change that to string
Line 15 is a cast to a long numeric data type ==> use cstr() or simply(trim()

Hope that helps

Priz
0
 

Author Comment

by:laffs_efc
ID: 35188619
Awesome! Cheers Pr1z!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to add a user-defined command button to the Windows 7 Explorer toolbar.  In the previous article (http://www.experts-exchange.com/A_2172.html), we saw how to put the Delete button back there where it belongs.  "Delete" is …
What my article will show is if you ever had to do processing to a listbox without being able to just select all the items in it. My software Visual Studio 2008 crystal report v11 My issue was I wanted to add crystal report to a form and show…
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now