Find value in range
 

Hi all,
I'm looking for an efficient way to find the row in a sheet with a matching
value in column A to a textbox value on a userform.

The userform is used to gather record information from the user and update
the spreadsheet. The spreadsheet can accumulate upwards of 100+ records and
the user form is the only way to navigate the data - I need a way to "jump"
to a specific record. The txtActivityNo would be the unique record identifier.

help?

Find value in range
 

Give this a try...

Sub FindActivityNumber()
Dim wksToSearch As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range

Set wksToSearch = Sheets("Sheet1")
Set rngToSearch = wksToSearch.Columns("A")
Set rngFound = rngToSearch.Find(What:=txtActivityNo, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry " & txtActivityNo & " was not found."
Else
wksToSearch.Select
rngFound.Select
End If
End Sub
--
HTH...

Jim Thomlinson


"TimT" wrote:

Hi all,
I'm looking for an efficient way to find the row in a sheet with a matching
value in column A to a textbox value on a userform.

The userform is used to gather record information from the user and update
the spreadsheet. The spreadsheet can accumulate upwards of 100+ records and
the user form is the only way to navigate the data - I need a way to "jump"
to a specific record. The txtActivityNo would be the unique record identifier.

help?

Find value in range
 

BRILLIANT!!!!
Works perfectly, thank you!

"Jim Thomlinson" wrote:

Give this a try...

Sub FindActivityNumber()
Dim wksToSearch As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range

Set wksToSearch = Sheets("Sheet1")
Set rngToSearch = wksToSearch.Columns("A")
Set rngFound = rngToSearch.Find(What:=txtActivityNo, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry " & txtActivityNo & " was not found."
Else
wksToSearch.Select
rngFound.Select
End If
End Sub
--
HTH...

Jim Thomlinson


"TimT" wrote:

Hi all,
I'm looking for an efficient way to find the row in a sheet with a matching
value in column A to a textbox value on a userform.

The userform is used to gather record information from the user and update
the spreadsheet. The spreadsheet can accumulate upwards of 100+ records and
the user form is the only way to navigate the data - I need a way to "jump"
to a specific record. The txtActivityNo would be the unique record identifier.

help?