.Find Type Mismatch Issue

AlohaExcel

New Member
Joined
Aug 28, 2019
Messages
2
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. :confused:

Rich (BB code):
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.
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,879
Office Version
365
Platform
Windows
You are missing a . from the Cells on this line
Code:
after:=[B][COLOR=#ff0000].[/COLOR][/B]Cells(5, 2)
although I wouldn't have expected that to give a Mismatch error
 
Last edited:

AlohaExcel

New Member
Joined
Aug 28, 2019
Messages
2
Aloha Fluff!

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

Rich (BB code):
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,879
Office Version
365
Platform
Windows
Remove the s from Rows on the last line of the Find.
Although you may not get the answer you expect.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,972
Messages
5,411,583
Members
403,380
Latest member
ifog671

This Week's Hot Topics

Top