I can't get the correct Rows to delete

[churley5]

Realise, that I know less about the problem than you do; What needs to be taken into account when optimising? Which sheet(s) should I be looking at?


I don't know how to do that. You'd better tell.


I've used Solver only a couple of times; I'm not overly familiar with it - however, it shouldn't be too difficult to get my head round.
Will it be allowed to reject orders altogether? Can a job be split among several plants? Or is it just a question of re-assigning plants to jobs?


…nor am I!


Your userform did all 4 mileage calculations, couldn't it be made to transfer them all to the sheet?


I see some rows in the sheet Week2Optimizied have 'Not Optimal' even though some values show a profit (rows 9 and 33)??

All in all I need a lot more info. My time is going to be more limited this coming week.

WOW!!! I get to answer a question!! I thought you guys on this forum practically wrote the program at this point.

I will be able to answer that for you in just a little bit, I don't want to throw out a one liner of an expalnation since you have done such a wonderful job explaining this vba stuff to me.

In the mean time take a look at the new spreadsheet. I have addded a new sheet. I have added some code to the button click for plant assignment.

I need to fill in cells 4, 5, 6, and 7 with the distances that are calculated from the form. Then I need the corresponding formulas AND formatting to fill.

Once this sheet is done, and since you have became so familar with it, I think describing solver will make more sense this way.

[churley5]

Ok so I got the distances to fill, I just can't get the formulas to fill. I have tried using different versions of this

[VBA]ActiveCell.AutoFill Destination:=Range(ActiveCell.Offset(0, 1),
ActiveCell.Offset(0, 1).End(xlDown)).Offset(0, -1)[/VBA]

but it keeps hanging.

[churley5]

This is what the code looks like.

[VBA]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
'Make sheet 1 Active
Sheets(1).Activate
'export Data to optimizization worksheet
Sheets(5).Activate
emptyRow2 = WorksheetFunction.CountA(Range("A:A")) + 3
Cells(emptyRow2, 1).Value = customerid.Value
Cells(emptyRow2, 2).Value = zipcode.Value
Cells(emptyRow2, 3).Value = Gallons.Value
Cells(emptyRow2, 4).Value = akrondistance.Value
Cells(emptyRow2, 5).Value = ftwaynedistance.Value
Cells(emptyRow2, 6).Value = rockforddistance.Value
Cells(emptyRow2, 7).Value = stlouisdistance.Value
'Fill Formulas
'???
[/VBA]