Using Input Bx to Find Record
 

I got this code to work and find a record in a range of data and select that
row. But it only finds numbers. I need it to find a string, or a letter-
number combination. What do I need to change?

Private Sub CommandButton6_Click()
ActiveSheet.Unprotect
Dim rngOrder As Range
Dim lngReferenceNumber As Long

lngReferenceNumber = Application.InputBox( _
Prompt:="Enter P.O. Number", _
Title:="Enter P.O. Number", _
Type:=1)

Set rngOrder = Worksheets("PO's").Range("A:A").Find( _
what:=lngReferenceNumber, _
lookat:=xlWhole)

If rngOrder Is Nothing Then
MsgBox "P.O. not found"
ActiveSheet.Protect
Else
With rngOrder
.Activate
Selection.Offset(0, 1).Select


End With
End If


End Sub

Using Input Bx to Find Record
 

One way:

Private Sub CommandButton6_Click()
Dim rngOrder As Range
Dim strReferenceNumber As String

strReferenceNumber = Application.InputBox( _
Prompt:="Enter P.O. Number", _
Title:="Enter P.O. Number", _
Type:=2)

Set rngOrder = ActiveSheet.Range("A:A").Find( _
what:=strReferenceNumber, _
lookat:=xlWhole)

If rngOrder Is Nothing Then
MsgBox "P.O. not found"
Else
ActiveSheet.Unprotect
rngOrder.Offset(0, 1).Select
End If
End Sub


In article <8fc8f484cd4a6@uwe, "bamenell" <u48632@uwe wrote:

I got this code to work and find a record in a range of data and select that
row. But it only finds numbers. I need it to find a string, or a letter-
number combination. What do I need to change?

Private Sub CommandButton6_Click()
ActiveSheet.Unprotect
Dim rngOrder As Range
Dim lngReferenceNumber As Long

lngReferenceNumber = Application.InputBox( _
Prompt:="Enter P.O. Number", _
Title:="Enter P.O. Number", _
Type:=1)

Set rngOrder = Worksheets("PO's").Range("A:A").Find( _
what:=lngReferenceNumber, _
lookat:=xlWhole)

If rngOrder Is Nothing Then
MsgBox "P.O. not found"
ActiveSheet.Protect
Else
With rngOrder
.Activate
Selection.Offset(0, 1).Select


End With
End If


End Sub

Using Input Bx to Find Record
 

the code should work on a string only if the string in the text box exactly
matches the cell. If the string in the box contains only a portion of the
cell data then make the following change

from:
xlWhole
to:
xLPart

"bamenell" wrote:

I got this code to work and find a record in a range of data and select that
row. But it only finds numbers. I need it to find a string, or a letter-
number combination. What do I need to change?

Private Sub CommandButton6_Click()
ActiveSheet.Unprotect
Dim rngOrder As Range
Dim lngReferenceNumber As Long

lngReferenceNumber = Application.InputBox( _
Prompt:="Enter P.O. Number", _
Title:="Enter P.O. Number", _
Type:=1)

Set rngOrder = Worksheets("PO's").Range("A:A").Find( _
what:=lngReferenceNumber, _
lookat:=xlWhole)

If rngOrder Is Nothing Then
MsgBox "P.O. not found"
ActiveSheet.Protect
Else
With rngOrder
.Activate
Selection.Offset(0, 1).Select


End With
End If


End Sub

Using Input Bx to Find Record
 

No -

by using Type:=1 in the InputBox method, he was restricting entries to
numbers.

Also by assigning the results to a Long variable, entering letters or an
alphanumeric combination would cause an error - the variable should be a
String variable.


In article ,
Joel wrote:

the code should work on a string only if the string in the text box exactly
matches the cell.

Using Input Bx to Find Record
 

This works just like I needed it to. Thank you very much!
--
Peace,
Brent

JE McGimpsey wrote:
One way:

Private Sub CommandButton6_Click()
Dim rngOrder As Range
Dim strReferenceNumber As String

strReferenceNumber = Application.InputBox( _
Prompt:="Enter P.O. Number", _
Title:="Enter P.O. Number", _
Type:=2)

Set rngOrder = ActiveSheet.Range("A:A").Find( _
what:=strReferenceNumber, _
lookat:=xlWhole)

If rngOrder Is Nothing Then
MsgBox "P.O. not found"
Else
ActiveSheet.Unprotect
rngOrder.Offset(0, 1).Select
End If
End Sub

I got this code to work and find a record in a range of data and select that
row. But it only finds numbers. I need it to find a string, or a letter-
[quoted text clipped - 27 lines]

End Sub

--
--
Peace,
Brent