Consulting

Results 1 to 4 of 4

Thread: Function does not work. Where is the mistake?

  1. #1

    Function does not work. Where is the mistake?

    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

  2. #2
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    Not far
    Posts
    1,550
    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")
    Be as you wish to seem

  3. #3
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,298
    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
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    Hi p45cal,

    that works great. Thank you very much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •