TaylorDavidI
Technical User
Hi guys,
I've only started working with VBA recently (probably about a week ago) and I'm trying to write a function I can call from any form I've created to determine the first available cell in a given sheet.
That sheet and the position to search within that range can be different every time I call it. This is the function I've written in the module I've named Functions:
Public Function FindEmpty(sheet As Sheets, start As Range) As Range
'MsgBox "Now entering function"
With sheet
If start = "" Then
' MsgBox "First cell is empty"
FindEmpty = start
ElseIf start.Offset(1, 0) = "" Then
' MsgBox "Second cell is empty"
FindEmpty = start.Offset(1, 0)
Else
' MsgBox "Some other cell is empty"
FindEmpty = start.End(xlDown).Offset(1, 0)
End If
End With
'MsgBox FindEmpty
End Function
The commented MsgBox lines were for me to track where the function fails; however, it never seems to even enter when I call it from a test macro:
Sub test()
MsgBox FindEmpty(Sheets("Sheet5"), [A1])
End Sub
I've also tried to declare Range and Object variables to set as the value returned by FindEmpty and then display those but the code fails every time.
Most of the time I get a type mismatch error but with the code as listed now I get the Runtime Error "9".
Any help would be appreciated
I've only started working with VBA recently (probably about a week ago) and I'm trying to write a function I can call from any form I've created to determine the first available cell in a given sheet.
That sheet and the position to search within that range can be different every time I call it. This is the function I've written in the module I've named Functions:
Public Function FindEmpty(sheet As Sheets, start As Range) As Range
'MsgBox "Now entering function"
With sheet
If start = "" Then
' MsgBox "First cell is empty"
FindEmpty = start
ElseIf start.Offset(1, 0) = "" Then
' MsgBox "Second cell is empty"
FindEmpty = start.Offset(1, 0)
Else
' MsgBox "Some other cell is empty"
FindEmpty = start.End(xlDown).Offset(1, 0)
End If
End With
'MsgBox FindEmpty
End Function
The commented MsgBox lines were for me to track where the function fails; however, it never seems to even enter when I call it from a test macro:
Sub test()
MsgBox FindEmpty(Sheets("Sheet5"), [A1])
End Sub
I've also tried to declare Range and Object variables to set as the value returned by FindEmpty and then display those but the code fails every time.
Most of the time I get a type mismatch error but with the code as listed now I get the Runtime Error "9".
Any help would be appreciated