PDA

View Full Version : [SOLVED] Function does not work. Where is the mistake?



Cinema
10-12-2018, 02:59 AM
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
10-12-2018, 07:15 AM
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
10-12-2018, 07:58 AM
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
10-16-2018, 03:00 AM
Hi p45cal,

that works great. Thank you very much!