Sir Phoenix
10-07-2005, 02:53 PM
Hey guys... I have a range. In column A are specific row titles. (Daytime1, daytime2, Daytime3, evening1, evening2, overnight1, overnight2, staticBob, staticMike, etc.) These values are automatically populated in a combobox, cmbSchedName. Anyways, I have a remove button. Based on the value of the combobox... if static(whatever) is selected, it's supposed to find the row, and delete the range. That works fine. If any others are pressed, for the ones with numbers on the end, it's supposed to go through the list (which is sorted), find the LAST instance of the schedule type (daytime, evening, overnight), and delete THAT title, but the selected schedule's data. (So if you choose to delete Daytime3 and there are 5 daytime schedules, Daytime3's data will be deleted, and Daytime4 will become the new daytime3, and Daytime5 will become daytime4, and daytime5's cell will be deleted in place of daytime3's. I'm getting an unknown error on this, so if you can help... much appreciated!
Private Sub cmdRemove_Click()
Dim cellRange, myCell, fooCell As Range
Dim strChecker As String
With ThisWorkbook.Sheets(4)
Set cellRange = .Range("a2", .Cells(Rows.Count, "a").End(xlUp))
Set myCell = cellRange.Find(Me.cmbSchedName.Value, MatchCase:=True)
If Me.cmbSchedName.Value = "" Then
MsgBox ("Please select a schedule.")
Else
If myCell Is Nothing Then
MsgBox ("The selected schedule has been already deleted!")
Else
MsgBox ("Schedule " & Me.cmbSchedName.Value & " has been deleted!")
If chkStatic.Value = True Then
.Range(myCell, .Cells(myCell.Row, "i")).Delete
Else
.Range(.Cells(myCell.Row, "b"), .Cells(myCell.Row, "i")).Delete
strChecker = Left(Me.cmbSchedName.Value, 4)
For Each fooCell In cellRange
If Left(fooCell.Text, 4) = strChecker And _
Left(.Range(.Cells(fooCell.Row + 1, "a")).Text, 4) <> strChecker Then
Set myCell = fooCell
End If
Next fooCell
myCell.Delete
fillScheds
End If
End If
End If
End With
End Sub
Private Sub cmdRemove_Click()
Dim cellRange, myCell, fooCell As Range
Dim strChecker As String
With ThisWorkbook.Sheets(4)
Set cellRange = .Range("a2", .Cells(Rows.Count, "a").End(xlUp))
Set myCell = cellRange.Find(Me.cmbSchedName.Value, MatchCase:=True)
If Me.cmbSchedName.Value = "" Then
MsgBox ("Please select a schedule.")
Else
If myCell Is Nothing Then
MsgBox ("The selected schedule has been already deleted!")
Else
MsgBox ("Schedule " & Me.cmbSchedName.Value & " has been deleted!")
If chkStatic.Value = True Then
.Range(myCell, .Cells(myCell.Row, "i")).Delete
Else
.Range(.Cells(myCell.Row, "b"), .Cells(myCell.Row, "i")).Delete
strChecker = Left(Me.cmbSchedName.Value, 4)
For Each fooCell In cellRange
If Left(fooCell.Text, 4) = strChecker And _
Left(.Range(.Cells(fooCell.Row + 1, "a")).Text, 4) <> strChecker Then
Set myCell = fooCell
End If
Next fooCell
myCell.Delete
fillScheds
End If
End If
End If
End With
End Sub