Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
application-defined or object defined error
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
application-defined or object defined error
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 "KJ MAN" skrev i meddelelsen ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Export a chart in a GIF file. Run-time error '1004': Application-defined or object-defined error; | Excel Programming | |||
Application-Defined or object defined error 1004 When ran on exel97 but not 2003 | Excel Programming | |||
Copying A Range of Cells From one Sheet to Another - Error Application Defined or Object Defined | Excel Programming | |||
Runtime error 1004- application defined or object defined erro | Excel Programming | |||
Runtime error 1004- application defined or object defined erro | Excel Programming |