.Find Type Mismatch Issue

[AlohaExcel]

Aloha all!

I've ran into a Type Mismatch Error when trying to 'set the last row' of a sheet. What I've done was create a separate public function that the original code should use. However, when it goes to the public function, the error arises. What I was hoping was the public function would provide a long data type.

Public Sub Adding_Clients()


'   Set variables
    Dim RevenueSheet As Worksheet
    Dim UnitsSheet As Worksheet
    Dim ClientSheet As Worksheet
    Dim RevenueLastRow As Long
    Dim RevenueFirstRow As Long
    Dim UnitsLastRow As Long
    Dim UnitsFirstRow As Long
    Dim ClientDetailLastRow As Long
    Dim ClientDetailFirstRow As Long
    
    Set RevenueSheet = ThisWorkbook.Worksheets("Revenue")
    Set UnitsSheet = ThisWorkbook.Worksheets("Units")
    Set ClientSheet = ThisWorkbook.Worksheets("Client Detail")
    
    **RevenueLastRow = SetLastRow(RevenueSheet)
    UnitsLastRow = SetLastRow(UnitsSheet)
    ClientDetailLastRow = SetLastRow(ClientSheet)
    
    RevenueFirstRow = RevenueSheet.Cells(6, "B").End(xlDown).Row
    
    UnitsFirstRow = UnitsSheet.Cells(6, "B").End(xlDown).Row
    
    ClientDetailFirstRow = ClientSheet.Cells(4, "B").End(xlDown).Row
    
'   Add new client to Revenue and Client Detail tabs


    Dim unit_client_range As Range
    Dim unit_team_range As Range
    Dim revenue_client As Range
    Dim revenue_team As Range
    Dim variable As Long
    Dim Client As Long
    
    Set unit_team_range = UnitsSheet.Range("C6:C" & UnitsLastRow)
    Set unit_client_range = UnitsSheet.Range("E6:E" & UnitsLastRow)
    Set revenue_team = RevenueSheet.Range("B6:B" & RevenueLastRow)
    Set revenue_client = RevenueSheet.Range("D6:D" & RevenueLastRow)

--- continued code not relevant ----

Public Function SetLastRow(Sheet As Worksheet) As Long


    Dim lng As Long


    If Application.WorksheetFunction.CountA(Sheet.Cells) <> 0 Then
        With Sheet
           ** lng = .Cells.Find(What:="*", _
                after:=Cells(5, 2), _
                LookIn:=xlFormulas, _
                Lookat:=xlPart, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Rows
        End With
    Else
        lng = 1
        
    End If
    
    SetLastRow = lng
                
End Function

Thanks in advance! I've marked the area when the error appears in asterisks and bold.

 

[Fluff]

You are missing a . from the Cells on this line

after:=[B][COLOR=#ff0000].[/COLOR][/B]Cells(5, 2)

although I wouldn't have expected that to give a Mismatch error

 

[AlohaExcel]

Aloha Fluff!

Mahalo for the advice! I've updated the code to show .cells however the error still most as a type mismatch.

Public Sub Adding_Clients()


'   Set variables
    Dim RevenueSheet As Worksheet
    Dim UnitsSheet As Worksheet
    Dim ClientSheet As Worksheet
    Dim RevenueLastRow As Long
    Dim RevenueFirstRow As Long
    Dim UnitsLastRow As Long
    Dim UnitsFirstRow As Long
    Dim ClientDetailLastRow As Long
    Dim ClientDetailFirstRow As Long
    
    Set RevenueSheet = ThisWorkbook.Worksheets("Revenue")
    Set UnitsSheet = ThisWorkbook.Worksheets("Units")
    Set ClientSheet = ThisWorkbook.Worksheets("Client Detail")
    
    ***RevenueLastRow = SetLastRow(RevenueSheet)
    UnitsLastRow = SetLastRow(UnitsSheet)
    ClientDetailLastRow = SetLastRow(ClientSheet)
    
    RevenueFirstRow = RevenueSheet.Cells(6, "B").End(xlDown).Row
    
    UnitsFirstRow = UnitsSheet.Cells(6, "B").End(xlDown).Row
    
    ClientDetailFirstRow = ClientSheet.Cells(4, "B").End(xlDown).Row

--- Not Releveant Code below ---


Public Function SetLastRow(Sheet As Worksheet) As Long


    Dim lng As Long


    If Application.WorksheetFunction.CountA(Sheet.Cells) <> 0 Then
        With Sheet
            lng = .Cells.Find(What:="*", _
                after:=.Cells(5, 2), _
                LookIn:=xlFormulas, _
                Lookat:=xlPart, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Rows
        End With
    Else
        lng = 1
        
    End If
    
    SetLastRow = lng
                
End Function

 

[Fluff]

Remove the s from Rows on the last line of the Find.
Although you may not get the answer you expect.