Function does not work. Where is the mistake?

[Cinema]

Hi,

my function should find a specific string and return the number of cells below.

The error appears in the line GetRows = Worksheets(....

Function GetRows(Category As String) As Long

Dim cl As Range

    With Worksheets(Sheet1).Cells
        Set cl = Cells.Find(Category, , xlValues, xlPart, , , False)
        If Not cl Is Nothing Then
            cl.Select
        End If
    End With
GetRows = Worksheets(Sheet1).Range(Selection, Selection.End(xlDown)).Count

End Function

[Aflatoon]

If sheet1 is the code name of the worksheet, you just use Sheet1, not Worksheets(Sheet1). If it's the tab name, it should be in quotes like this: Worksheets("Sheet1")

[p45cal]

1.What's the error?
2.You want a count of rows (not cells)?
3.What do you want it to return if the string isn't found.
4.Currently it searches the active sheet (assuming the code isn't in a sheet code-module) because the code within the With…End With doesn't use it.
5.Selecting will fail if it's not the active sheet.
5.Have you defined Sheet1 as a variable elsewhere? Normally you'd expect to see Worksheets("Sheet1") or Sheet1.
Without trying it out perhaps:

Function GetRows(Category As String) As Long
Dim cl As Range
GetRows = 0 'what it returns if nothing is found.
With Worksheets("Sheet1").Cells
Set cl = .Cells.Find(Category, , xlValues, xlPart, , , False)
If Not cl Is Nothing Then
GetRows = Range(cl, cl.End(xlDown)).Rows.Count
End If
End With
End Function


For copy/pasting:

Function GetRows(Category As String) As Long
Dim cl As Range
GetRows = 0    'what it returns if nothing is found.
With Worksheets("Sheet1")
  Set cl = .Cells.Find(Category, , xlValues, xlPart, , , False)
  If Not cl Is Nothing Then
    GetRows = Range(cl, cl.End(xlDown)).Rows.Count
  End If
End With
End Function

[Cinema]

Hi p45cal,

that works great. Thank you very much!