Searching in Excel

[Guest]

I am trying to design a module where the user would insert a number in the
search box and when the user presses the search button it would populate the
respective text boxes. The textboxes are on a UserForm. In the following
code when running I get the following error "Object variable or with block
variable not set". Any help would be greatly appreciated and a big thank you.

Private Sub cmdSearch_Click()
Dim wrkSheet As Worksheet
Dim wrkRng As Range
Dim intRow As Integer

Set wrkSheet = Worksheets("DataFile")

With wrkSheet
Set wrkRng = wrkSheet.Columns(1).Find(txtLocSrch)
intRow = wrkRng.Row <=============== Error Line
wrkSheet.Cells(intRow, 1) = txtLocation
wrkSheet.Cells(intRow, 2) = txtHost
wrkSheet.Cells(intRow, 3) = txtDistrict
wrkSheet.Cells(intRow, 4) = txtRespClass
End With
Set wrkSheet = Nothing

 

[Dave Peterson]

I'd check to see if the .Find was successful:

Private Sub cmdSearch_Click()
Dim wrkSheet As Worksheet
Dim wrkRng As Range
Dim intRow As Integer

Set wrkSheet = Worksheets("DataFile")

With wrkSheet
Set wrkRng = wrkSheet.Columns(1).Find(txtLocSrch)
if wkrRng is nothing then
'not found, msgbox???
else
intRow = wrkRng.Row <=============== Error Line
wrkSheet.Cells(intRow, 1) = txtLocation
wrkSheet.Cells(intRow, 2) = txtHost
wrkSheet.Cells(intRow, 3) = txtDistrict
wrkSheet.Cells(intRow, 4) = txtRespClass
end if
End With
Set wrkSheet = Nothing

========
One more thing, you may want to specify all the parms in the .find statement.
If you don't, then excel will use whatever was last used--either by your code or
by the user with the previous Edit|Find.

 

[Guest]

After adding and modifying a few lines I came up with the following solution;
but I am now getting the error "Type Mismatch". Here is the new code - thank
you for your help in this situation

Dim rngFind As Range

If txtLocation.Text = vbNullString Then
Worksheets("Messages").Range("F2") = "Enter a Location and " &
Chr$(10) & _
"Click Search"
Worksheets("Messages").Range("F3") = 64
Worksheets("Messages").Range("F4") = "Search"
Worksheets("Messages").Range("F5") = 1
Call ErMsgBox
Exit Sub
End If

Application.ScreenUpdating = False
Sheets("DataFile").Select
Set rngFind =
Sheets("DataFile").Range("A:A").Find(What:=txtLocation.Text,
After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False)

If rngFind Is Nothing Then
Worksheets("Messages").Range("F2") = "Reference number is " &
Chr$(10) & _
"not found"
Worksheets("Messages").Range("F3") = 64
Worksheets("Messages").Range("F4") = "Reference"
Worksheets("Messages").Range("F5") = 1
Call ErMsgBox
Else
txtHost.Text = rngFind.Offset(0, 1).Value
txtDistrict = rngFind.Offset(0, 2).Value
txtRespClass = rngFind.Offset(0, 3).Value
End If
End Sub

 

[Dave Peterson]

What line?

And what do those variables used on that troublesome line contain?

 

[Guest]

I would like to thank you for your help in resolving this problem. In
looking through the code it came to me when using offset it starts at 0 not
1. Now my routine works. Again thank you for you assistance.

 

[Dave Peterson]

Glad I could help <vbg>.

I would like to thank you for your help in resolving this problem. In
looking through the code it came to me when using offset it starts at 0 not
1. Now my routine works. Again thank you for you assistance.