ScottyBee
03-17-2021, 04:28 PM
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
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