application-defined or object defined error

[KJ MAN]

here's my code

If Worksheets("selector").Range("b5").Value2 = "" Then
Set startrange = Range("b5")
ElseIf Worksheets("selector").Range("b6").Value2 = "" Then
Set startrange = Range("b6")
ElseIf Worksheets("selector").Range("b7").Value2 = "" Then
Set startrange = Range("b7")
ElseIf Worksheets("selector").Range("b8").Value2 = "" Then
Set startrange = Range("b8")
ElseIf Worksheets("selector").Range("b9").Value2 = "" Then
Set startrange = Range("b9")
ElseIf Worksheets("selector").Range("b10").Value2 = "" Then
Set startrange = Range("b10")
ElseIf Worksheets("selector").Range("b11").Value2 = "" Then
Set startrange = Range("b11")
ElseIf Worksheets("selector").Range("b12").Value2 = "" Then
Set startrange = Range("b12")
ElseIf Worksheets("selector").Range("b13").Value2 = "" Then
Set startrange = Range("b13")
ElseIf Worksheets("selector").Range("b14").Value2 = "" Then
Set startrange = Range("b14")
ElseIf Worksheets("selector").Range("b15").Value2 = "" Then
Set startrange = Range("b15")
Else: info = MsgBox("Sorry, you may only enter 10 songs at a time!",
vbOKOnly, "Maximum Songs Input")
End If
snginp = MsgBox("Please verify " + song + " " + folder + " " + volume + "
" + disk + " " + num, vbYesNo, "Verify Input")
If snginp = vbYes Then
**** Worksheets("selector").Range(startrange).Value2 = song ****
nextrange = startrange.Offset(rowoffset:=0, columnoffset:=1)
Worksheets("selector").Range(nexttrange).Value2 = folder
nextrange = nextrange.Offset(rowoffset:=0, columnoffset:=1)
Worksheets("selector").Range(nextrange).Value2 = volume
nextrange = nextrange.Offset(rowoffset:=0, columnoffset:=1)
Worksheets("selector").Range(nextrange).Value2 = disk
nextrange = nextrange.Offset(rowoffset:=0, columnoffset:=1)
Worksheets("selector").Range(nextrange).Value2 = num


Error occurs at the line enclosed in **** ****

I need my input to be based on the first blank cell in a range.

Any suggestions?

 

[Per Jessen]

Hi

As StartRange already is a range you don't need the Range statement. Also,
you need a test to verify if the limit is exceeded, and exit the macro if it
is.

The code below should do it:

Dim StartRange As Range
For r = 5 To 15
If Worksheets("selector").Range("B" & r).Value2 = "" Then
Set StartRange = Worksheets("selector").Range("B" & r)
Exit For
End If
Next
If StartRange Is Nothing Then
info = MsgBox("Sorry, you may only enter 10 songs at a time!", vbOKOnly,
"Maximum Songs Input")
Exit Sub
End If
snginp = MsgBox("Please verify " & song + " " & folder + " " & volume + " "
& Disk + " " & num, vbYesNo, "Verify Input")
If snginp = vbYes Then
With Worksheets("selector")
StartRange = song
StartRange.Offset(0, 1).Value2 = folder
StartRange.Offset(0, 2).Value2 = volume
StartRange.Offset(0, 3).Value2 = Disk
StartRange.Offset(0, 4).Value2 = num
End With
End If

Regards,
Per