Function to Pass Workbook and Worksheet objects giving Type Mismatch Error 13

[ScottyBee]

Hello,

I have a function that finds the last used row of a worksheet. This function is "hard coded" to only work with a given worksheet. I want to make this function more versatile by being able to pass in a workbook and worksheet object to find it's last row.

In the code block below, the Function LastRowOfWorksheet works fine and is hard coded.

The function LastRowOfWorksheet2 receives a workbook and worksheet object when called. I am getting type mismatch error 13 and cannot figure out why. The code generates the error when the Test Sub is ran.

lRow = Workbooks(wkb).Worksheets(wsh).Cells.Find(What:="*", _

In the Test Sub, the debug.print does return the workbook name and worksheet name correctly in the Immediate Window.

Any help is greatly appreciate--thanks :)


Function LastRowOfWorksheet() As Integer
Dim lRow As Long
Dim lCol As Long


lRow = Workbooks("Apprentice Hours.xlsm").Worksheets("Allowed_Users").Cells.Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

LastRowOfWorksheet = lRow

End Function

Function LastRowOfWorksheet2(wkb As Workbook, wsh As Worksheet) As Integer
Dim lRow As Long
Dim lCol As Long


lRow = Workbooks(wkb).Worksheets(wsh).Cells.Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Debug.Print "test"
LastRowOfWorksheet2 = lRow
End Function

Sub Test()
Dim wkb As Workbook, wsh As Worksheet
Set wkb = Workbooks("zDontUSE_ApprenticeRelatedTraininghours.xlsm")
Set wsh = Workbooks("zDontUSE_ApprenticeRelatedTraininghours.xlsm").Worksheets("Appr. new")
Debug.Print wkb.Name, wsh.Name
Debug.Print LastRowOfWorksheet2(wkb, wsh)

End Sub

[SamT]

You are mixing Integers and Longs in the same code

Also, you are sending the workbook twice, once as wkb and again as part of wsh

[Paul_Hossler]

Just a quick look

In this function calling parameters ...



Function LastRowOfWorksheet2(wkb As Workbook, wsh As Worksheet) As Integer
Dim lRow As Long
Dim lCol As Long
lRow = Workbooks(wkb).Worksheets(wsh).Cells.Find(What:="*", _

you're passing a workbook and a worksheet

So it should be something like



lRow = wkb.wsh.Cells.Find(What:="*", _


or


lRow = wsh.Cells.Find(What:="*", _

Using Worksheets( ...) it should be a string = the .Name, but you're passing it the worksheet object

[ScottyBee]

Thanks Paul for the reply. Yes, that makes sense. I adjusted my code to be:


Function LastRowOfWorksheet2(wkb As Workbook, wsh As Worksheet) As Integer
Dim lRow As Long
Dim lCol As Long


lRow = Workbooks(wkb.Name).Worksheets(wsh.Name).Cells.Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
LastRowOfWorksheet2 = lRow
End Function

Sub Test()
Dim wkb As Workbook, wsh As Worksheet
Set wkb = Workbooks("zDontUSE_ApprenticeRelatedTraininghours.xlsm")
Set wsh = Workbooks("zDontUSE_ApprenticeRelatedTraininghours.xlsm").Worksheets("Appr. new")
Debug.Print LastRowOfWorksheet2(wkb, wsh)

End Sub



It now works great. Thank you very much :)

[Paul_Hossler]

1. You could use the WB and WS objects as parameters, and skip the .Name

2. In


Function LastRowOfWorksheet2(wkb As Workbook, wsh As Worksheet) As Integer

It would be better to return a Long just in case there are more that 32,767 rows

[ScottyBee]

Thanks John, I just changed the data type from Integer to Long. Great catch. :)

[SamT]

Option Explicit

Function LastRowOfWorksheet2(wsh As Worksheet) As Long
Dim lRow As Long
Dim lCol As Long

lRow = wsh.Cells.Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
LastRowOfWorksheet2 = lRow
End Function

Sub Test()
Dim wsh As Worksheet
Set wsh = Workbooks("zDontUSE_ApprenticeRelatedTraininghours.xlsm").Worksheets("Appr. new")
Debug.Print wsh.Parent.Name, wsh.Name
Debug.Print LastRowOfWorksheet2(wsh)

End Sub

[ScottyBee]

Thanks Sam and Paul for your great posts that helped me to make my code work. I forgot to mark this post as "Solved".