I can't get the correct Rows to delete

[churley5]

Ok I'm going to post the new updated code. I have went with the suggestions of removing the first command button, doing the code inside the vba, etc.

Currently I'm trying to add some more detail to the user form. #, of orders, gallons processed, plant utilizations, etc. Everything was working until I added the code in the Akrondistance_change sub. I am get a type mismatch error.

Here is the code. The debugger highlights the akronprocessed.value line.

I will attached the file as well.


[VBA]Private Sub akrondistance_Change()
'E is oneway miles = akrondistance,ftwaynedistance,rockforddistance,stlouisdistance
'C is gallons = gallons.value
'=C26*Constants!$B$15+Constants!$B$16-(ROUNDUP(C26/Constants!$B$11,0))*(((4*E26)*SUM(Constants!$B$9:Constants!$B$10))+(Constan ts!$B$7*2))
If IsNumeric(gallons.Value) And IsNumeric(akrondistance.Value) Then
akronorderprofit = profit(gallons.Value, akrondistance.Value)
SelectMaxProfit


AkronOrders.Value = [COUNTIF(Week1.Data[Processing Location],"Akron")+1]
akronprocessed.Value = [SUMIF(Week1.Data[Processing Location],"Akron",Week1.Data[Gallons])] + gallons.Value
processingtimeakron.Value = akronprocessed.Value / [Constants B5]
akrontotalhours.Value = processingtimeakron.Value + AkronOrders.Value

End If
End Sub

Private Sub ftwaynedistance_Change()
If IsNumeric(gallons.Value) And IsNumeric(ftwaynedistance.Value) Then
ftwayneorderprofit = profit(gallons.Value, ftwaynedistance.Value)
SelectMaxProfit
End If
End Sub

Private Sub rockforddistance_Change()
If IsNumeric(gallons.Value) And IsNumeric(rockforddistance.Value) Then
rockfordorderprofit = profit(gallons.Value, rockforddistance.Value)
SelectMaxProfit
End If
End Sub

Private Sub stlouisdistance_Change()
If IsNumeric(gallons.Value) And IsNumeric(stlouisdistance.Value) Then
stlouisorderprofit = profit(gallons.Value, stlouisdistance.Value)
SelectMaxProfit
End If
End Sub
Private Sub gallons_Change()
If IsNumeric(gallons.Value) Then
If IsNumeric(ftwaynedistance.Value) Then ftwayneorderprofit = profit(gallons.Value, ftwaynedistance.Value)
If IsNumeric(rockforddistance.Value) Then rockfordorderprofit = profit(gallons.Value, rockforddistance.Value)
If IsNumeric(stlouisdistance.Value) Then stlouisorderprofit = profit(gallons.Value, stlouisdistance.Value)
If IsNumeric(akrondistance.Value) Then akronorderprofit = profit(gallons.Value, akrondistance.Value)
SelectMaxProfit
End If
End Sub
Function profit(gals, dist)
a = gals * [Constants!$B$15] + [Constants!$B$16]
b = (Application.WorksheetFunction.RoundUp(gallons.Value / [Constants!$B$11], 0)) '*
c = ((4 * dist) * [SUM(Constants!$B$9:Constants!$B$10)]) + [Constants!$B$7*2]
profit = a - b * c
End Function
Function utilization(h, y)
'capacity
y = [Constants!$B$3]
'number of orders
n = [COUNTIF(Week1!$D:$D,B2)] + 1
'total processing time
t = [SUMIF(Week1!$D:$D,B2,Week1!$C:$C)] + gallons.Value / [Constants!$B$5]
'total operating hours
h = n + t
utilization = h / y
End Function

Sub SelectMaxProfit()
If IsNumeric(ftwayneorderprofit) Then f = CDbl(ftwayneorderprofit) Else f = 0
If IsNumeric(rockfordorderprofit) Then r = CDbl(rockfordorderprofit) Else r = 0
If IsNumeric(stlouisorderprofit) Then s = CDbl(stlouisorderprofit) Else s = 0
If IsNumeric(akronorderprofit) Then a = CDbl(akronorderprofit) Else a = 0
maxProfit = Application.Max(f, r, s, a)
If maxProfit = f Then ftwayne.Value = True
If maxProfit = r Then rockford.Value = True
If maxProfit = s Then stlouis.Value = True
If maxProfit = a Then akron.Value = True
End Sub
Private Sub CommandButton2_Click()
Dim emptyRow As Long
'Make Sheet2 Active
Sheets(2).Activate
'Determine emptyRow
emptyRow1 = WorksheetFunction.CountA(Range("A:A")) + 1
For Each ctrl In Me.Frame1.Controls
If TypeName(ctrl) = "OptionButton" Then
If ctrl Then
x = Application.Match(ctrl.Name, Array("Akron", "Fort Wayne", "Rockford", "Saint Louis"), 1)
PlantName = Choose(x, "Akron", "Fort Wayne", "Rockford", "Saint Louis")
DistCtrlName = Choose(x, "akrondistance", "FtWaynedistance", "Rockforddistance", "StLouisdistance")
End If
End If
Next ctrl
'Export Data to worksheet
Cells(emptyRow1, 1).Value = customerid.Value
Cells(emptyRow1, 2).Value = zipcode.Value
Cells(emptyRow1, 3).Value = gallons.Value
Cells(emptyRow1, 4).Value = PlantName
Cells(emptyRow1, 5).Value = Me.Controls(DistCtrlName).Value
End Sub
[/VBA]