Searching in Excel
 

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

--
Larry E. Brueshaber

Searching in Excel
 

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.

Larry wrote:

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

--
Larry E. Brueshaber

--

Dave Peterson

Searching in Excel
 

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:=txtLoca tion.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

--
Larry E. Brueshaber


"Larry" wrote:

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

--
Larry E. Brueshaber

Searching in Excel
 

What line?

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

Larry wrote:

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:=txtLoca tion.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

--
Larry E. Brueshaber

"Larry" wrote:

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

--
Larry E. Brueshaber

--

Dave Peterson

Searching in Excel
 

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.
--
Larry E. Brueshaber


"Larry" wrote:

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

--
Larry E. Brueshaber

Searching in Excel
 

Glad I could help <vbg.


Larry wrote:

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.
--
Larry E. Brueshaber

"Larry" wrote:

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

--
Larry E. Brueshaber

--

Dave Peterson