Run time error 424

[rinijg]

Hi, I was trying to run the a code for which" run time error 424, object required" error occured. While debugging i found that the error is in Set rsearch line...
Dim rSearch As Range 'range to search
Worksheets("database").Activate
Set rSearch = database.Range("a6", Range("a65536").End(xlUp))

Could you please tell me what to do to clear that error?
Thanks a lot <!-- google_ad_section_end --><!-- / message -->

 

[Comfy]

database need to be classed as a worksheet object.

Dim rSearch As Range 'range to search
Dim database As worksheet
Set database = ActiveWorkbook.Worksheets("database")
Set rSearch = database.Range("a6", Range("a65536").End(xlUp))

 

[rinijg]

Thanks a lot... now that error is cleared. Another error occured.
Runtime error 9, Subscript out of range.
What should i do now? I have absolutely no clue

 

[Comfy]

Debug again and see where it fails.

It might due to the database sheet not being active.

Dim rSearch As Range 'range to search
Dim database As worksheet
Set database = ActiveWorkbook.Worksheets("database")
database.activate
Set rSearch = database.Range("a6", Range("a65536").End(xlUp))

/Comfy

 

[rinijg]

when i debugged first time, this error was shown after
Set database = ActiveWorkbook.Worksheets("database") was executed. Now when i was debugging, it showed another error. Runtime error 1004, application defined or object defined error. This error was shown after executing Set rSearch = database.Range("a3", Range("a65536").End(xlUp))

This code is written in a coomand button in another worksheet. That is there is a command button in sheet1, which on clicking runs the above code. Is it because of that, this error is being shown, or??/

Thanks a lot for ur immediate reply

 

[Comfy]

Did you try the updated code in previous post?

 

[rinijg]

yeah...i updated the code n still its showing runtime error 1004, application defined or object defined error

 

[Comfy]

and does the sheet "database" exist?

I can't seem to replicate your error.

 

[rinijg]

yeah... I have the sheet called database. Let me put in detail what i am trying to do. In the sheet database, i have so many data about different project. I am trying to write a code for finding about the data about a particular project, by searching using project name which is in the worksheet "Estimation", Range ("B2"). If found, this data will be pasted in Find_Results worksheets.

following is the code. The command button 1 is in the worksheet "Estimation"

Private Sub CommandButton1_Click()
Dim strFind As String 'what to find
Dim FirstAddress As String
Dim rSearch As Range 'range to search
Dim database As Worksheet
Set database = ActiveWorkbook.Worksheets("database")
database.Activate
Set rSearch = database.Range("a6", Range("a65536").End(xlUp))
Dim f As Integer
strFind = Worksheets("Estimation").Range("B2").Value 'what to look for

With rSearch
Set c = .Find(strFind, LookIn:=xlValues)
If Not c Is Nothing Then 'found it
c.Select

For i = 0 To 31

Worksheets("Find_Result").Cells(3, i + 1).Value = c.Offset(0, i).Value
Next i

f = 0
FirstAddress = c.Address
Do
f = f + 1 'count number of matching records
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
If f > 1 Then
Select Case MsgBox("There are " & f & " instances of " & strFind, vbOKCancel Or vbExclamation Or vbDefaultButton1, "Multiple entries")
Case vbOK
FindAll
Case vbCancel
'do nothing
End Select

End If
Else: MsgBox strFind & " not listed" 'search failed
End If
End With
Worksheets("database").Activate
End Sub


Sub FindAll()
Dim strFind As String 'what to find
Dim rFilter As Range 'range to search
Set rFilter = Worksheets("database").Range("a3", Range("ap65536").End(xlUp))
Set rng = Worksheets("database").Range("a1", Range("a65536").End(xlUp))
strFind = Worksheets("Estimation").Range("B2").Value
With Worksheets("database")
If Not .AutoFilterMode Then .Range("A3").AutoFilter
rFilter.AutoFilter Field:=1, Criteria1:=strFind
Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
'Me.ListBox1.Clear
j = 3
For Each c In rng

For i = 0 To 31

Worksheets("Find_Result").Cells(3, i + 1).Value = c.Offset(0, i).Value
Next i
j = j + 1
Next c
End With
Worksheets("database").Activate
End Sub


I really hope that you can help me out....

 

[Comfy]

When posting Code please use the

 tags, makes for an easier read :)

I put that code into a workbook with four sheets, named:-

-Sheet1
-Estimation
-database
-Find_Result

And the code correctly found the search value in the database range and copied it to the Find_Result Sheet.

I'm stumped!