object required Error

Hey I have a search button that is supposed to find a certain string in an excel worksheet, when I click it it gives me this error:

Compile Error:

Object Required

Heres my code so far
Option Explicit
Dim xlsApp As Excel.Application

Private Sub cmdCalc_Click()
Dim strBookCode As String
Dim strComments As String
Dim lngBinNumber As Long
Dim strInitials As String
Dim curTotalWeight As Currency
Dim lngTareWeight As Long
Dim lngDividers As Long
Dim curSampleWeight As Currency
Dim lngNumOnBin As Long
Dim sngBookPounds As Single
Dim sngNumBooks As Single
Dim LastEntry As String
Dim LastNum As Integer
Dim LastWrap As String
xlsApp.ActiveSheet.Cells(xlsApp.ActiveCell.Row, 1).Activate
Do Until xlsApp.ActiveCell.Value = ""
xlsApp.ActiveCell.Offset(1).Activate
Loop
LastEntry = xlsApp.ActiveCell.Offset(-1, 0).Value
LastNum = xlsApp.ActiveCell.Offset(-1, 2).Value
LastWrap = xlsApp.ActiveCell.Offset(-1, 1).Value
lblDate2 = Date
strBookCode = txtBookCode.Text
strComments = txtComments.Text
xlsApp.ActiveSheet.Cells(xlsApp.ActiveCell.Row, 1).Activate
strInitials = txtInitials.Text
curTotalWeight = CCur(txtTotalWeight.Text)
lngTareWeight = CLng(txtTareWeight.Text)
lngDividers = CLng(txtDividers.Text)
curSampleWeight = CCur(txtSampleWeight.Text)
lngNumOnBin = CLng(txtNumOnBin.Text)
sngBookPounds = curTotalWeight - lngTareWeight - (lngDividers * 0.64)
sngNumBooks = sngBookPounds / curSampleWeight * 50
lblBinNumber2 = lngBinNumber
lblNumBooks2 = FormatNumber(sngNumBooks, 0)
xlsApp.ActiveCell.Offset(0, 0) = txtBookCode.Text
xlsApp.ActiveCell.Offset(0, 1) = txtComments.Text
xlsApp.ActiveCell.Offset(0, 3) = lblDate2
xlsApp.ActiveCell.Offset(0, 4) = txtInitials.Text
xlsApp.ActiveCell.Offset(0, 5) = txtTotalWeight.Text
xlsApp.ActiveCell.Offset(0, 6) = txtTareWeight.Text
xlsApp.ActiveCell.Offset(0, 8) = txtDividers.Text
xlsApp.ActiveCell.Offset(0, 9) = txtSampleWeight.Text
xlsApp.ActiveCell.Offset(0, 11) = lblNumBooks2
xlsApp.ActiveSheet.Cells(xlsApp.ActiveCell.Row, 1).Activate
If xlsApp.ActiveCell.Value = LastEntry And xlsApp.ActiveCell.Offset(0, 1).Value = LastWrap Then
lblBinNumber2 = LastNum + 1
Else
lblBinNumber2 = 1
End If
xlsApp.ActiveCell.Offset(0, 2) = lblBinNumber2
xlsApp.ActiveWorkbook.Save
xlsApp.Workbooks.Close
xlsApp.Quit


End Sub

Private Sub cmdExit_Click()
xlsApp.Workbooks.Close
xlsApp.Quit
End
End Sub



Private Sub cmdSearch_Click()
With Worksheets(1).Range("a1:a500")
Dim c As String
Set c = .Find(txtBookCode.Text, lookin:=xlValues) 'error occurs here (the "c =" is highlighted in the error)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub

Private Sub Form_Load()
Set xlsApp = Excel.Application
xlsApp.Dialogs(xlDialogOpen).Show
End Sub

Private Sub mnuWarehouse_Click()
frmWareHouse.lblJobNumber = txtJobNumber.Text
frmWareHouse.lblPCNumber = txtBookCode.Text
frmWareHouse.lblQty = lblNumBooks2
frmWareHouse.lblName2 = txtInitials.Text
frmWareHouse.lblDate2 = Date
frmWareHouse.Show

End Sub

Private Sub optProjectComplete_Click(Index As Integer)
Select Case Index
Case 0
frmWareHouse.lblProjComp2 = "Yes"
Case 1
frmWareHouse.lblProjComp2 = "No"
End Select
End Sub

Dim C as a Range instead of a String.

Dim C as a Range instead of a String.

that worked good, thanks, now im getting this error:

RunTime error 92:

Object variable or with block variable not set.

and heres my code now

Private Sub cmdSearch_Click()
With Worksheets(1).Range("a1:a500")
Dim c As Range
Dim firstAddress As Range
Set c = .Find(txtBookCode.Text, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub

also, if I ever get this to work, where will the search result output too?

Address returns a string not a range object so

Dim firstAddress As String

Address returns a string not a range object so

Dim firstAddress As String

That seemed to work because I didnt get any errors but where did the search get outputted to?

I believe that it is actually output to a filesearch object. :)

Your code, which if I remember correctly is the example from the help file, 'outputs' the found items by making the Interior Pattern of the cells in which the value of txtBookCode.Text is found Gray.

Here is the code explained in detail.

Set c = .Find(txtBookCode.Text, lookin:=xlValues)
'If nothing matches c is Nothing
'Otherwise c is the cell that you've found a match
If Not c Is Nothing Then
'The address of the first matching cell
firstAddress = c.Address
Do

'Set the cell's interior of the found item = Gray
c.Interior.Pattern = xlPatternGray50
'Find the next cell containing txtBookCode.Text
'If something is found c will now be the cell containing the new match
Set c = .FindNext(c)
'Keep looping untill c is nothing or you loop back to the first cell you found
Loop While Not c Is Nothing And c.Address <> firstAddress
End If

So is there any code I can put in to make the data of the found cell appear in a text box on my form? I'm guessing I would set the text box equal to "c" but I'm not sure. Thanks for your help so far.

Just set the textbox equal to C.Address.

thanks :)

Just set the textbox equal to C.Address.
Don't you mean c.Value instead of C.Address?

Don't you mean c.Value instead of C.Address?

Yeah, I misread what he was asking, I guess. I thought he wanted to know where it was found, not what was all found in the cell that had the string he was searching for.

Yeah, I misread what he was asking, I guess. I thought he wanted to know where it was found, not what was all found in the cell that had the string he was searching for.
I guess, he could use the address as the address for the linked cell property if his textbox has that property.

Will the code loop through all the worksheets?

No, you need to loop through the Worksheets
For ws=i to Worksheets.count
worksheets(ws).activate
'code
next

I'm getting an "Object Variable or with block variable not set" again on this code:

Private Sub cmdSearch_Click()
With Worksheets(1).Range("a1:a500")
Dim c As Range
Dim firstAddress As String
Set c = .Find(txtBookCode.Text, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
txtResults.Text = c.Value
End With

the "txtResults.Text = c.Value" is highlighted. Your help has been much appreciated!

That's because of where your txtResults.Text = c.Value statement is.
Where it is located c could equal Nothing. Move it inside the if statement.

And by the way you don't need c.Interior.Pattern = xlPatternGray50 for the code to work.

Thanks for your help, I'm not getting the error any more, but nothing is displayed in my Results textbox still, any ideas on what might be wrong? I put the code inside the If statement here:

If Not c Is Nothing Then
firstAddress = c.Address
txtResults.Text = c.Value
Do

Set c = .FindNext(c)

Loop While Not c Is Nothing And c.Address <> firstAddress
End If

I also tried putting it right above the end if statement but it didn't display anything there either.

Code in a worksheet:

Private Sub CommandButton1_Click()
With Worksheets(1).Range("a1:a500") 'range your looking in
Dim c As Range
Dim firstAddress As String
Set c = .Find("Blitz84", LookIn:=xlValues) 'what you're lokking after (Blitz84)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 6 'will make the matching cell yellow
Range("B1").Value = firstAddress 'the cel address of the cel where "blitz" whas found in
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

End Sub

Play around with the code (depends of what you want)
or take another look on the forum.
as said before ther are a lot of examples to find.

italkid

If I wanted to find the string that was in a textbox could I put text1.Text in place of "blitz84"?

Something you where looking for i think...
This code will search thru a compleet workbook no matter how much sheets it have.

Implement the code in the project your are working on.
You'll have to make some changes then.

If you want to try this out (in Excel),put a button on one of the sheets,
Put this in your button code :

Private Sub CommandButton1_Click()
SearchAfter
End Sub

Insert a module and put this code in the module :

Sub SearchAfter()
Dim SearchIn As Range
Dim LookForwhat As String
Dim CountSheet As Integer
Dim BlitzHome As String
Dim SelectSheet As Boolean

LookForwhat = InputBox("What are you looking for ?")
If LookForwhat = "" Then Exit Sub

For CountSheet = 1 To Worksheets.Count
Set SearchIn = Worksheets(CountSheet).Cells.Find(LookForwhat)
If SearchIn Is Nothing = False Then
BlitzHome = SearchIn.Address
Do
Set SearchIn = Worksheets(CountSheet).Cells.FindNext(SearchIn)
MsgBox "Found in cell " & SearchIn.Address(False, False) & _
" on sheet " & SearchIn.Parent.Name
Loop While Not SearchIn Is Nothing And SearchIn.Address <> BlitzHome

SelectSheet = True
End If
Next CountSheet

If SelectSheet = False Then
MsgBox "Found nothing ! "
End
End If
End Sub


Hope this helps

italkid.

That code works right and has got me going in the right direction but I have a couple questions (sorry...). Is there any way at all that I can Take what the end user puts into a text box called "txtBookCode" and use that as a search string? Also, is there a way to get the data from the last found cell into a text box? For example, if I have a string found in cell A65 can I get the data from that cell into a textbox or just get excel to make that cell the active cell? Sorry for all the questions. Thanks for your help so far.

Just change the InputBox into your "TxtBookCode" and
the MsgBox into a ListBox ore a set to Multiline TextBox.

Like this

LookForwhat = TxtBookCode.Text

'and

ListBox1.AddItem SearchIn.Address(False, False) & _
" " & SearchIn.Parent.Name

Don't understand why you want to return the same string you are looking for into a List- or TextBox...

italkid.

The reason I want to do this is because I don't want the people at work that use this program to have to deal with Excel at all. Also there are some criteria that I want them to be able to check in order to get a correct bin number. Basically, it's stuff specific to my job that I need to do. Don't worry, there isa method to my madness... Is there any way I can get the actual value that's in the found cells into the list box?

" Is there any way I can get the actual value that's in the found cells into the list box?"

Thats the part i don't understand.
You want to look for a string > the code will find that string and you want to return the found value (the same string) to a listbox.

In general you use the "Find" Method to look after no matter what in a Workbook or Sheet.
Once the item found you use other code to do whatever you want > copy,paste,delete,replace and so on.

If you look for any cell with "Blitz" in it,the found value will be "Blitz" so ? or do i misunderstand you....

Maybe because i need some sleep now.

italkid.

Its a bit hard to explain... At work we weigh up bins that are full of books and enter them into the computer using Excel. I'm creating a front end for excel that will make it much more user friendly. We enter the bins by book code and the number of the bin, but we don't always finish projects before we start another one. So, for example, we 're running a book with the code Q0502 and a person weighs up the 5th bin of the project, they would enter the code into my form and my VB project should search for the last cell with that same book code then just add 1 to the bin number. That's probably really confusing to you but thats what I'm trying to do.

No, that was nicely explained. However, let me see if I've got it straight.

You want to Find the Last Cell with the BookCode looping through all the Worksheets in the Workbook. Once the last cell containing that Bookcode is found you wish to Add 1 to it's Bin number.

Dim c As Range
Dim firstAddress As String
Dim InSheet As Long
For i = 1 To Worksheets.Count 'range your looking in
Worksheets(i).Activate
With Worksheets(i).Columns(1)
Set c = .Find("Q0502", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Activate
'Store last sheet that you found something
InSheet = i
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
'Reset first address for the new sheet
firstAddress = ""
End If
End With
Next
'If found
If InSheet > 0 Then
'Return to Worksheet containing last found cell
Worksheets(InSheet).Activate
'Assuming Bin number is in Column B
ActiveCell.Offset(0, 1).Value = _
ActiveCell.Offset(0, 1).Value + 1
Else
MsgBox "Not found"
End If

Yes, thats what I'm looking for (with a few adjustments that I could probably do by myself, if I can't then I'll be back...) the only problem I'm having is I get a Type Mismatch error here:

Dim i As Range
For i = 1 To Worksheets.Count

I also tryed to Dim the i as a string but I got the same error.

You Count the worksheets so...
Dim i As Integer

For i = 1 To Worksheets.Count

yup, that worked, thanks.