PDA

View Full Version : Solved: Finding a record



Kicker
08-08-2004, 04:01 PM
I have a worksheet with 4 columns. The first col is a number in text format and sorted ascending order. The second col is a name

I have a userform where I enter a number and either on commandbutton click or textbox_change, I want to "find" the appropriate number and get the associated name for a text box on the userform.

then, I will be using another command button to create a record in another worksheet.

When the following runs, it gives me a 1 for the value of both r and c. Any ideas of how to get to the appropriate record?


Private Sub cmdSetRecord_Click()
Dim strName As String
Dim r, c As Integer
Worksheets("GunNumbers").Select
strName = CStr(Me.txtGunNum.Text)
Range("A2:A500").Find strName
r = ActiveCell.Row
c = ActiveCell.Column
MsgBox ("Pause to check values")
End Sub

ttfn
Kicker

Kicker
08-08-2004, 04:28 PM
I got the example in the Helpfiles to work. Need a lot of modification to do what I want to end up doing. But...it looks as though I answered my own questions.

Thanks for your time...


With Worksheets("GunNumbers").Range("a1:a500")
Set vCell = .Find(Me.txtGunNum, LookIn:=xlValues)
If Not vCell Is Nothing Then
firstAddress = vCell.Address
Range(vCell.Address).Select
Me.txtName.Text = ActiveCell.Offset(0, 1).Value
End If
End With

ttfn
Kicker

Daniel Klann
08-08-2004, 04:37 PM
Hello,

When you use the Find method it won't move the cursor, it will return a range object containing the value you were looking for. You should be able to use something like


Private Sub cmdSetRecord_Click()
Dim strName As String
Dim r, c As Integer
Dim rngFind As Range
Worksheets("GunNumbers").Select
strName = CStr(Me.txtGunNum.Text)
Set rngFind = Range("A2:A500").Find(what:=strName)
r = rngFind.Row
c = rngFind.Column
MsgBox ("Pause to check values")
End Sub

You may also want to include some error handling in case the number is not found:-


Private Sub cmdSetRecord_Click()
Dim strName As String
Dim r, c As Integer
Dim rngFind As Range
Worksheets("GunNumbers").Select
strName = CStr(Me.txtGunNum.Text)
Set rngFind = Range("A2:A500").Find(what:=strName)
If rngFind Is Nothing Then
MsgBox "Could not find that Gun Number", vbExclamation
Exit Sub
End If

r = rngFind.Row
c = rngFind.Column
MsgBox ("Pause to check values")
End Sub


HTH
Dan

Jacob Hilderbrand
08-08-2004, 04:47 PM
Hi

First a couple things I notice here.

1) Dim r, c As Integer

This declares c as an Integer and r as variant. You need Dim r As Integer, c As Integer , but they should be on seperate lines.

2) Always use Long instead of Integer. It can store larger numbers (needed for Rows) and it actually runs faster than Integer would.

3) When asking for help on userforms you should avoid naming everthing, since it forces anyone who wants to help to go through and name all the textboxes etc. to match for the code to work. Alternately attaching a wb makes it much easier.

4) When using .Find you really should state the LookIn portion as values or formulas since if you don't VBA will do the default which could be either one (based on the last search). I have had searches fail for this reason and spent a lot of time trying to figure out why.

I see you changed this in your second post, so nevermind :)

Ok, enough preaching. Here is your solution. Post back if this works for you or not.


Option Explicit
Private Sub cmdSetRecord_Click()

Dim strName As String
Dim r As Long
Dim c As Long
Dim Rng1 As Range

strName = CStr(Me.txtGunNum.Text)
With Worksheets("GunNumbers").Range("A2:A500")
Set Rng1 = .Find(what:=strName, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True)
End With

If Rng1 Is Nothing Then
MsgBox "Not Found"
Else
r = Rng1.Row
c = Rng1.Column
MsgBox ("Pause to check values: Column: " & c & " Row: " & r)
End If
End Sub

Kicker
08-08-2004, 05:56 PM
Jacob:
I have been playing with it since I made the post and actually came up somewhat close to what you had. Exception::::::Your's is a whole lot cleaner and more compact.

I am also going to use your set rng1 idea.

Dan:
I like your idea of a msgbox / error warning and will also use that.

Much appreciated
ttfn
Kicker
:hi: