Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Runtime Error 9: Subscript out of range 1

Status
Not open for further replies.

TaylorDavidI

Technical User
Apr 20, 2007
36
CA
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 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
 
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
 
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...
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top