Runtime Error 9: Subscript out of range 1

[TaylorDavidI]

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

 

[PHV]

I see 2 problems (at least):
Public Function FindEmpty(sheet As [!]Worksheet[/!], start As Range) As Range

MsgBox FindEmpty(Sheets("Sheet5"), [!]Sheets("Sheet5").[/!][A1])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TaylorDavidI]

Wonderful! The function works now, the only other error was the missing "Set" commands but I've added those.

I also removed the first variable "sheet" since the range is specific to a worksheet anyway; thanks for the help

 

[christhedonstar]

You might want to use debug.print statements instead of msgbox statements when you're debugging your code... The results will appear in the immediate window... As well as stepping through your code instead of running it until it fails...