Finding a record

[Kicker]

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]

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]

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]

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.

[Edit] I see you changed this in your second post, so nevermind [/edit]

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]

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