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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.