PDA

View Full Version : I can't get the correct Rows to delete



churley5
11-10-2012, 08:32 AM
Ok, Just started out with VBA, and am in a class actually. Thus this is my homework.

I got this far on my own, then asked the teacher for some help. The answer was not what I wanted to hear. Which was its not possible for this to work. You have to do it this very particular way.

I just can't believe that.

In essence I have created a user form, that a user recieves and order and inputs 4 variables and hits enter. It calculates some stuff, and the user makes another decision from the same form with four radio buttons. Hits another command button and it assignes the order.

My issue is once the user selects the radio button, I can't get it to delete the correct rows.

I really don't want to start all over on this, because I feel and am very close.

Any help?

I didn't want to cut and paste the VBA here because its pretty lengthy.
So here is the file.

patel
11-10-2012, 10:34 AM
I suppose the macro in question is CommandButton2_Click()

what is akron_checked ?
the name of radio button is akron, then you have to write
if akron.value = True Then
...............
why did you write Range("A1").End(xlDown).EntireRow.Select
wich cells you want to select ?

churley5
11-10-2012, 10:40 AM
Yes it is the command button 2.

Yes the akron_checked is a radio button.

I have gotten closer currently the VBA is this


Private Sub CommandButton2_Click()


'Make Sheet2 Active
Sheets(2).Activate

'Akron Selection Clear others
If akron_checked = True Then

If WorksheetFunction.CountA(ActiveSheet.Cells) = 0 Then Exit Sub

'Worksheet is empty
Else

x = ActiveSheet.Cells.Find("*", [A1], xlFormulas, xlPart, _
xlByRows, xlPrevious, False, False).Row

'delete last row, Row above last, row above that
ActiveSheet.Cells(x, 1).EntireRow.Delete
ActiveSheet.Cells(x - 1, 1).EntireRow.Delete
ActiveSheet.Cells(x - 2, 1).EntireRow.Delete

End If

'Ftwayne selection Clear others
If ftwayne_checked = True Then
If WorksheetFunction.CountA(ActiveSheet.Cells) = 0 Then Exit Sub

'Worksheet is empty
Else

x = ActiveSheet.Cells.Find("*", [A1], xlFormulas, xlPart, _
xlByRows, xlPrevious, False, False).Row

'delete last row, Row above last, row above that
ActiveSheet.Cells(x, 1).EntireRow.Delete
ActiveSheet.Cells(x - 1, 1).EntireRow.Delete
ActiveSheet.Cells(x - 3, 1).EntireRow.Delete

End If
'Rockfor selection clear others
If rockford_checked = True Then
If WorksheetFunction.CountA(ActiveSheet.Cells) = 0 Then Exit Sub

'Worksheet is empty
Else

x = ActiveSheet.Cells.Find("*", [A1], xlFormulas, xlPart, _
xlByRows, xlPrevious, False, False).Row

'delete last row, Row above last, row above that
ActiveSheet.Cells(x, 1).EntireRow.Delete
ActiveSheet.Cells(x - 2, 1).EntireRow.Delete
ActiveSheet.Cells(x - 3, 1).EntireRow.Delete

End If
'stlouis selection clear others
If stlouis_Checked = True Then
If WorksheetFunction.CountA(ActiveSheet.Cells) = 0 Then Exit Sub

'Worksheet is empty
Else

x = ActiveSheet.Cells.Find("*", [A1], xlFormulas, xlPart, _
xlByRows, xlPrevious, False, False).Row

'delete last row, Row above last, row above that
ActiveSheet.Cells(x - 2, 1).EntireRow.Delete
ActiveSheet.Cells(x - 1, 1).EntireRow.Delete
ActiveSheet.Cells(x - 3, 1).EntireRow.Delete

End If

End Sub

patel
11-10-2012, 12:40 PM
Yes it is the command button 2.

Yes the akron_checked is a radio button.

you are wrong, see attached

churley5
11-10-2012, 12:51 PM
Ok I see.

SO I updated the code to include the correct name of the radio buttons.

However when I execute the commandbutton2. Instead of deleting the 3 rows that should be deleted for that particular button, it executes all four radio buttons.

So I need to only execute one of them. Have I missed an IF/Then/Else somewhere? If I delete for instance the code for the other cities ('ftwayne selection clear others), and only leave the code for one city. it operates correctly.

Sorry if these are basic questions this is just the 4th class on VBA.

Thanks. UPdated code is this.


Private Sub CommandButton1_Click()
Dim emptyRow As Long

'Make Sheet2 Active
Sheets(2).Activate
'Determine emptyRow
emptyRow1 = WorksheetFunction.CountA(Range("A:A")) + 1
emptyRow2 = WorksheetFunction.CountA(Range("A:A")) + 2
emptyRow3 = WorksheetFunction.CountA(Range("A:A")) + 3
emptyRow4 = WorksheetFunction.CountA(Range("A:A")) + 4
'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 = "Akron"
Cells(emptyRow1, 5).Value = akrondistance.Value
Cells(emptyRow2, 1).Value = customerid.Value
Cells(emptyRow2, 2).Value = zipcode.Value
Cells(emptyRow2, 3).Value = gallons.Value
Cells(emptyRow2, 4).Value = "Fort Wayne"
Cells(emptyRow2, 5).Value = ftwaynedistance.Value
Cells(emptyRow3, 1).Value = customerid.Value
Cells(emptyRow3, 2).Value = zipcode.Value
Cells(emptyRow3, 3).Value = gallons.Value
Cells(emptyRow3, 4).Value = "Rockford"
Cells(emptyRow3, 5).Value = rockforddistance.Value
Cells(emptyRow4, 1).Value = customerid.Value
Cells(emptyRow4, 2).Value = zipcode.Value
Cells(emptyRow4, 3).Value = gallons.Value
Cells(emptyRow4, 4).Value = "Saint Louis"
Cells(emptyRow4, 5).Value = stlouisdistance.Value


End Sub

Private Sub CommandButton2_Click()


'Make Sheet2 Active
Sheets(2).Activate

'Akron Selection Clear others
If akron = True Then

If WorksheetFunction.CountA(ActiveSheet.Cells) = 0 Then Exit Sub

'Worksheet is empty
Else

x = ActiveSheet.Cells.Find("*", [A1], xlFormulas, xlPart, _
xlByRows, xlPrevious, False, False).Row

'delete last row, Row above last, row above that
ActiveSheet.Cells(x, 1).EntireRow.Delete
ActiveSheet.Cells(x - 1, 1).EntireRow.Delete
ActiveSheet.Cells(x - 2, 1).EntireRow.Delete

End If

'Ftwayne selection Clear others
If ftwayne = True Then
If WorksheetFunction.CountA(ActiveSheet.Cells) = 0 Then Exit Sub

'Worksheet is empty
Else

x = ActiveSheet.Cells.Find("*", [A1], xlFormulas, xlPart, _
xlByRows, xlPrevious, False, False).Row

'delete last row, Row above last, row above that
ActiveSheet.Cells(x, 1).EntireRow.Delete
ActiveSheet.Cells(x - 1, 1).EntireRow.Delete
ActiveSheet.Cells(x - 3, 1).EntireRow.Delete

End If
'Rockfor selection clear others
If rockford = True Then
If WorksheetFunction.CountA(ActiveSheet.Cells) = 0 Then Exit Sub

'Worksheet is empty
Else

x = ActiveSheet.Cells.Find("*", [A1], xlFormulas, xlPart, _
xlByRows, xlPrevious, False, False).Row

'delete last row, Row above last, row above that
ActiveSheet.Cells(x, 1).EntireRow.Delete
ActiveSheet.Cells(x - 2, 1).EntireRow.Delete
ActiveSheet.Cells(x - 3, 1).EntireRow.Delete

End If
'stlouis selection clear others
If stlouis = True Then
If WorksheetFunction.CountA(ActiveSheet.Cells) = 0 Then Exit Sub

'Worksheet is empty
Else

x = ActiveSheet.Cells.Find("*", [A1], xlFormulas, xlPart, _
xlByRows, xlPrevious, False, False).Row

'delete last row, Row above last, row above that
ActiveSheet.Cells(x - 2, 1).EntireRow.Delete
ActiveSheet.Cells(x - 1, 1).EntireRow.Delete
ActiveSheet.Cells(x - 3, 1).EntireRow.Delete

End If

End Sub

p45cal
11-10-2012, 04:00 PM
You're using the sheet as a calculator to add all the 4 plants just to get values to appear in the userform. Then you have to find and delete 3 of them. Why not let the userform do the calculations, leaving the sheet untouched, and only add the chosen plant to the sheet?
See attached.

Teeroy
11-10-2012, 04:32 PM
You are trying to test the radio button object, what you need is to test the .value property of the object. Also you're checking for a blank worksheet in each radio button option; just do it once at the beginning. Check the following changes to CommandButton2_Click. I've added in one error check so you can get the idea and add your own.

I'm not going to change the style of the programming since it is homework.

Private Sub CommandButton2_Click()



'Make Sheet2 Active
Sheets(2).Activate
'First check for empty worksheet
If WorksheetFunction.CountA(ActiveSheet.Cells) = 0 Then Exit Sub
'Next check that an order has been entered

last4entries = Join(Application.Transpose(Range("D" & Rows.Count).End(xlUp).Offset(-3, 0).Resize(4, 1)), ",")
If last4entries <> Join(Array("Akron", "Fort Wayne", "Rockford", "Saint Louis"), ",") Then
MsgBox "An order must be entered before it is assigned to a plant"
Exit Sub
End If


'Akron Selection Clear others
If akron.Value Then

x = ActiveSheet.Cells.Find("*", [A1], xlFormulas, xlPart, _
xlByRows, xlPrevious, False, False).Row

'delete last row, Row above last, row above that
ActiveSheet.Cells(x, 1).EntireRow.Delete
ActiveSheet.Cells(x - 1, 1).EntireRow.Delete
ActiveSheet.Cells(x - 2, 1).EntireRow.Delete

End If

'Ftwayne selection Clear others
If ftwayne.Value Then

x = ActiveSheet.Cells.Find("*", [A1], xlFormulas, xlPart, _
xlByRows, xlPrevious, False, False).Row

'delete last row, Row above last, row above that
ActiveSheet.Cells(x, 1).EntireRow.Delete
ActiveSheet.Cells(x - 1, 1).EntireRow.Delete
ActiveSheet.Cells(x - 3, 1).EntireRow.Delete

End If
'Rockfor selection clear others
If rockford.Value Then

x = ActiveSheet.Cells.Find("*", [A1], xlFormulas, xlPart, _
xlByRows, xlPrevious, False, False).Row
MsgBox x 'TESTING ONLY!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'delete last row, Row above last, row above that
ActiveSheet.Cells(x, 1).EntireRow.Delete
ActiveSheet.Cells(x - 2, 1).EntireRow.Delete
ActiveSheet.Cells(x - 3, 1).EntireRow.Delete

End If
'stlouis selection clear others
If stlouis.Value Then

x = ActiveSheet.Cells.Find("*", [A1], xlFormulas, xlPart, _
xlByRows, xlPrevious, False, False).Row

'delete last row, Row above last, row above that
ActiveSheet.Cells(x - 1, 1).EntireRow.Delete
ActiveSheet.Cells(x - 2, 1).EntireRow.Delete
ActiveSheet.Cells(x - 3, 1).EntireRow.Delete

End If

End Sub

churley5
11-10-2012, 08:10 PM
You're using the sheet as a calculator to add all the 4 plants just to get values to appear in the userform. Then you have to find and delete 3 of them. Why not let the userform do the calculations, leaving the sheet untouched, and only add the chosen plant to the sheet?
See attached.

WOW!! THANKS. That is how I wanted it to work, but we have not gotten that far yet. the change() code is what I am assuming allows it to calcualte on the form itself. That and the coded formulas. I had thought about doing the calculations in the code. But this is like a 6 week long project. So it started out as a really simple model. Then each week it gets added to. So I was trying to force what I did in a previous week, to work for this week. Might not have been a good idea.

Thanks for your help....

churley5
11-10-2012, 08:33 PM
You are trying to test the radio button object, what you need is to test the .value property of the object. Also you're checking for a blank worksheet in each radio button option; just do it once at the beginning. Check the following changes to CommandButton2_Click. I've added in one error check so you can get the idea and add your own.

I'm not going to change the style of the programming since it is homework.

Private Sub CommandButton2_Click()



'Make Sheet2 Active
Sheets(2).Activate
'First check for empty worksheet
If WorksheetFunction.CountA(ActiveSheet.Cells) = 0 Then Exit Sub
'Next check that an order has been entered

last4entries = Join(Application.Transpose(Range("D" & Rows.Count).End(xlUp).Offset(-3, 0).Resize(4, 1)), ",")
If last4entries <> Join(Array("Akron", "Fort Wayne", "Rockford", "Saint Louis"), ",") Then
MsgBox "An order must be entered before it is assigned to a plant"
Exit Sub
End If


'Akron Selection Clear others
If akron.Value Then

x = ActiveSheet.Cells.Find("*", [A1], xlFormulas, xlPart, _
xlByRows, xlPrevious, False, False).Row

'delete last row, Row above last, row above that
ActiveSheet.Cells(x, 1).EntireRow.Delete
ActiveSheet.Cells(x - 1, 1).EntireRow.Delete
ActiveSheet.Cells(x - 2, 1).EntireRow.Delete

End If

'Ftwayne selection Clear others
If ftwayne.Value Then

x = ActiveSheet.Cells.Find("*", [A1], xlFormulas, xlPart, _
xlByRows, xlPrevious, False, False).Row

'delete last row, Row above last, row above that
ActiveSheet.Cells(x, 1).EntireRow.Delete
ActiveSheet.Cells(x - 1, 1).EntireRow.Delete
ActiveSheet.Cells(x - 3, 1).EntireRow.Delete

End If
'Rockfor selection clear others
If rockford.Value Then

x = ActiveSheet.Cells.Find("*", [A1], xlFormulas, xlPart, _
xlByRows, xlPrevious, False, False).Row
MsgBox x 'TESTING ONLY!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'delete last row, Row above last, row above that
ActiveSheet.Cells(x, 1).EntireRow.Delete
ActiveSheet.Cells(x - 2, 1).EntireRow.Delete
ActiveSheet.Cells(x - 3, 1).EntireRow.Delete

End If
'stlouis selection clear others
If stlouis.Value Then

x = ActiveSheet.Cells.Find("*", [A1], xlFormulas, xlPart, _
xlByRows, xlPrevious, False, False).Row

'delete last row, Row above last, row above that
ActiveSheet.Cells(x - 1, 1).EntireRow.Delete
ActiveSheet.Cells(x - 2, 1).EntireRow.Delete
ActiveSheet.Cells(x - 3, 1).EntireRow.Delete

End If

End Sub


Ok so first thing, is why can't you enter more than one order? After you enter an order then assign it to a plant, and you enter a second order it overrights the last order you entered?

Second is I'm not sure what this code does>
last4entries = Join(Application.Transpose(Range("D" & Rows.Count).End(xlUp).Offset(-3, 0).Resize(4, 1)), ",")
If last4entries <> Join(Array("Akron", "Fort Wayne", "Rockford", "Saint Louis"), ",")

thanks for your help!!!
just when i think I am picking some of this up, I get turned around.

Teeroy
11-10-2012, 11:28 PM
Ok so first thing, is why can't you enter more than one order? After you enter an order then assign it to a plant, and you enter a second order it overrights the last order you entered? Because of the way you wrote your code it enters the order to be in all 4 locations then deletes the incorrect ones. I don't get the last order overwritten when I run it. (I did find I left a test msg in Rockford, sorry)



Second is I'm not sure what this code does>
last4entries = Join(Application.Transpose(Range("D" & Rows.Count).End(xlUp).Offset(-3, 0).Resize(4, 1)), ",")
If last4entries <> Join(Array("Akron", "Fort Wayne", "Rockford", "Saint Louis"), ",")
thanks for your help!!!
just when i think I am picking some of this up, I get turned around. This is a basic error check. It makes a string of the plants in the last 4 rows and compares against the string you would get for a new entry. In all likelihood if it matches then you've entered new data. For a full entry check you should check a primary key such as a PO number but you don't have one. It does this because if you haven't entered a new entry before you assign the plant then you'll delete 3 out of the last 4 lines of data.

With your userform I would used a different logic and only written 1 line with no plant with button1, then used button2 to enter the plant. The check then is simply whether you have a plant assigned to the last row before you move on to the next order.

churley5
11-11-2012, 10:09 AM
How would I make a text box execute a formula and then add one to the result.

For instance would it be something similar to:

Private Sub AkronOrders_Change()
AkronOrders.Value = Application.WorksheetFunction.CountIf(Sheet(2), Range("D:D"), "Akron") + 1

End Sub

p45cal
11-11-2012, 11:14 AM
sure, you could use
AkronOrders.Value = Application.WorksheetFunction.CountIf(Sheets("Week1").Range("D:D"), "Akron") + 1but you wouldn't do it on the change event for the same control (as you have it), but perhaps on another control's change event or on some button click.

edit post posting:It might be safer to confine the count to within the table instead of the whole of column D of that sheet:AkronOrders.Value = [COUNTIF(Week1.Data[Processing Location],"Akron")+1]

churley5
11-11-2012, 12:51 PM
sorry for all these questions. I am really trying to learn this stuff, so I don't want to just cut and paste the code everyone is giving me.

so I have written this code. to change during another event. But I keep getting an error and i'm not sure why.

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

THe number of orders calculates correctly. Obviously because I used the code that was given to me. So the code I added is the issue. What am I missing here. It calculates correctly for the second line, but then gives me an error and doesn't move to the third.

p45cal
11-11-2012, 01:32 PM
this:
processingtimeakron.Value = akronprocessed.Value / [Constants B5] should be:
processingtimeakron.Value = akronprocessed.Value / [Constants!B5]
I'm not sure why, though, you want to add the result of that calculation (a number of hours) to the count of the number of times "Akron" appears in column D plus 1… they're not the same units

churley5
11-11-2012, 02:32 PM
this:
processingtimeakron.Value = akronprocessed.Value / [Constants B5] should be:
processingtimeakron.Value = akronprocessed.Value / [Constants!B5]
I'm not sure why, though, you want to add the result of that calculation (a number of hours) to the count of the number of times "Akron" appears in column D plus 1… they're not the same units

Yeah I know, its the way the problem is written.

Basically for each order it take one hour for change over time. So essentially the number of orders = change over time.

Thanks for the help.... I almost have this weeks behind. Then I can get started early on next weeks. Which I am sure I will need assistance on. The next version of this is to use a "google API??" for the zip code and have it automatically calcuatle the one way distance.

churley5
11-11-2012, 04:41 PM
this:
processingtimeakron.Value = akronprocessed.Value / [Constants B5] should be:
processingtimeakron.Value = akronprocessed.Value / [Constants!B5]
I'm not sure why, though, you want to add the result of that calculation (a number of hours) to the count of the number of times "Akron" appears in column D plus 1… they're not the same units

Ok it is still hanging on the second line.

This is what that sub looks like:
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

Its doing the calculation as far as adding the new order to the current orders, but it gives an error at the second line.

p45cal
11-11-2012, 04:59 PM
Ok it is still hanging on the second line.

Its doing the calculation as far as adding the new order to the current orders, but it gives an error at the second line.By the second line do you mean the one beginning If… or the one beginning akronorderprofit =… ?

And can you say what the error message says?

churley5
11-11-2012, 05:17 PM
By the second line do you mean the one beginning If… or the one beginning akronorderprofit =… ?

And can you say what the error message says?

The message is " Could not set the Value property. Type mismatch."

and when the debug starts it has this line highlighted in yellow

akronprocessed.Value = [SUMIF(Week1.Data[Processing Location],"Akron",Week1.Data[Gallons]) + gallons.value]

p45cal
11-12-2012, 12:49 AM
Try moving a closing square bracket:
akronprocessed.Value = [SUMIF(Week1.Data[Processing Location],"Akron",Week1.Data[Gallons])] + gallons.value

churley5
11-12-2012, 06:28 AM
Try moving a closing square bracket:
akronprocessed.Value = [SUMIF(Week1.Data[Processing Location],"Akron",Week1.Data[Gallons])] + gallons.value


Nope same error. I'm going to post an updated code.

churley5
11-12-2012, 06:43 AM
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.


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

p45cal
11-12-2012, 06:58 AM
same error, different line:
processingtimeakron.Value = akronprocessed.Value / [Constants!B5]

churley5
11-12-2012, 07:07 AM
same error, different line:
processingtimeakron.Value = akronprocessed.Value / [Constants!B5]

DEAR LORD!!!!

Thanks so much. I have been looking at this too long. I have to take a break. How long did it take you guys to become proficent with vba?

This is painful...I the teacher is trying, but its the first time he has taught the class. So there is some growing pains.

churley5
11-12-2012, 05:47 PM
Where do you find the settings for a text box on a user form.

I need something to be formatted as a percentage instead of a decimal. I have looked through all of the properties and for the life of me can not figure it out.

does it have to be coded as a percentage some how?

p45cal
11-12-2012, 06:34 PM
You have to format the text yourself - it is, after all, a textbox, not a cell in a sheet. Check out the vba Format function.
If you see 49.5% on a sheet and the cell is formatted as a percentage, the actual value in the cell is 0.495 or thereabouts. To get the same result in vba use something along the lines of:
~.value = format(.495,"00.0%")
or if the value .495 is in a variable x then
~.value = format(x,"00.0%")
or you can use an expression instead:
~.value = format(.4+zz/34*p,"00.0%")

The result of the format function is a string so excel might not always recognise it as a numeric value.

churley5
11-12-2012, 09:40 PM
Ok. This was working, and I added some formatting to the user form. And now i'm getting a type mismatch error. for all of the `utilization.Value = etc... lines of code.

I tried using a hard value of 120 vs. the [Constants!B3] and that didn't help. I thought I was almost done with this and now this. I was hoping to move on to adding the Google API portion. But first thing first.

Here is the code, the line marked with **** is highlighted in yellow in the debugger.

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 = Format([COUNTIF(Week1.Data[Processing Location],"Akron")+1], "Fixed")
akronprocessed.Value = Format([SUMIF(Week1.Data[Processing Location],"Akron",Week1.Data[Gallons])] + gallons.Value, "Fixed")
processingtimeakron.Value = Format(akronprocessed.Value / [Constants!B5], "Fixed")
akrontotalhours.Value = Format(processingtimeakron.Value + AkronOrders.Value, "Fixed")

****akronutilization.Value = Format(akrontotalhours.Value / [Constants!B3], "Fixed")****


End If
End Sub

p45cal
11-13-2012, 02:21 AM
I'm out and about today without a machine with Excel 2010 on it so I can't test (table formulae on the sheet have changed since Excel 2003).
I suggest when you are in debug mode after encountering the error you break down the problem in the Immediate pane (Ctrl+g if you can't see it);
type:
?akrontotalhours.Value / [Constants!B3]
and press Enter. Does it complain still? If so, type:
?akrontotalhours.Value
and press Enter; does it give the result you expect?

You can also look at the Locals pane (Alt+v, s if you can't see it, or find it in the View menu) which will show you the variables. With this, you can break down the problem further; add code like:
x=akrontotalhours.Value
y=[Constants!B3]
z=x/y
zz=format(z,"fixed")If you're using Option Explicit you can temporarily comment it out to save having to Dim the variables. Put the above before the problem line then examine the Locals pane when the problem line errors.
You can use F8 to step through code line by line.
You should soon find the problem.
When I get to a suitably equipped machine I'll have a look.

churley5
11-13-2012, 06:47 AM
If IsNumeric(gallons.Value) And IsNumeric(akrondistance.Value) Then
akronorderprofit = profit(gallons.Value, akrondistance.Value)
SelectMaxProfit

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

x = akrontotalhours.Value
yy = [Constants!B3]
Z = x / yy
zz = Format(Z, "fixed")
akronutilization.Value = Format(akrontotalhours.Value / [Constants!B3], "0.00%")

I did what you said I think.

Now it highlights the Z= x/yy

It gives the correct value for akrontotalhours.vale

So the [Constants!B3] is the issue? I replaced it and tried to just use the number the cell is referencing. Same issue.

p45cal
11-13-2012, 08:39 AM
When I ran your code in msg#28 and came up with the error I saw that the value of x was
"70.9715.00"
which is clearly wrong, so I was a bit surprised you said:
It gives the correct value for akrontotalhours.vale. It comes from akrontotalhours.Value which in turn comes from:
akrontotalhours.Value = Format(processingtimeakron.Value + AkronOrders.Value, "Fixed")
so it seems that the processingtimeakron.Value + AkronOrders.Value were being treated as strings and not as values. There is no chance that Excel can treat a string with two decimal points in as a number so we get the type mismatch error. To coerce Excel into converting the strings into values we have to:
akrontotalhours.Value = Format(CDbl(processingtimeakron.Value) + CDbl(AkronOrders.Value), "Fixed") You may find you have to do this in other places too.

Another thing came to my attention, the line:
akronutilization.Value = Format(akrontotalhours.Value / [Constants!B3], "0.00%")
didn't seem to be putting a percentage sign into the textbox - at first I thought that maybe this was due to assigning this to the .value property, so I changed it to .Text… to no avail. Then I noticed that the ControlSource property of the textbox was Akron.Utilization which, from Help, means: "If you change the Value of the control, the change is automatically reflected in the linked cell or field. Similarly, if you change the value of the linked cell or field, the change is automatically reflected in the Value of the control."
It was this two-way connection which was losing the formatting. Breaking this connection had the percentage sign showing properly in the textbox.

Now I'm not sure that you want this behaviour before one of the plants is assigned the job, because I guess you don't want to update the values in Manager's page until then. You have several choices:
1. break the connection by setting the ControlSource to nothing, but the cell will need a formula in (I'm guessing it may already have had one in the past, but that went long ago, overwritten by the form version!) and calculate the value in the code (you can grab it by using range("Akron.Utilization").Value before adding something to it).
2. keep the connection but don't try to update the value in the form's code (but you won't know a putative value for that plant with the new customer).
3. A bit of 1 & 2; break the connection, have a formula in that cell, get the value from the sheet in the code, add the difference the new customer would make to show the putative value in the textbox. Now, it is only when you assign a plant to the job, data gets added to a sheet, the formula in the sheet will automatically update the value there (which, if the formula is working properly, should end up the same as the calculated value in the textbox).

Finally, note that the problem was fixed by breaking the problem down into smaller pieces which immediately showed where the fault lay.

churley5
11-13-2012, 08:46 AM
I really appreciate your help.

I think the real issue here is our teacher isn't showing any debugging techniques.

I'm going to try and wrap my head around all of what you just posted. See if I can fix it myself and get this thing working again. what gets me was it was working flawlessly, and I was just making formating changes, and all of a sudden it didn't work.

I have no idea what went wrong.

Again Thanks

p45cal
11-13-2012, 08:59 AM
I have no idea what went wrong. ??!!
I told you:
so it seems that the processingtimeakron.Value + AkronOrders.Value were being treated as strings and not as values processingtimeakron.Value was "70.79" - a string
AkronOrders.Value was "15.00" - another string
the code was concatenating the two (the plus sign) leaving
"70.7915.00"
which can never be treated by Excel as a number, hence type mismatch.

I said as much in msg#25!:
The result of the format function is a string so excel might not always recognise it as a numeric value.

churley5
11-13-2012, 10:04 AM
??!!
I told you: processingtimeakron.Value was "70.79" - a string
AkronOrders.Value was "15.00" - another string
the code was concatenating the two (the plus sign) leaving
"70.7915.00"
which can never be treated by Excel as a number, hence type mismatch.

I said as much in msg#25!:

I understand what is wrong now....what I really meant was I didn't know what happened because 1) all I thought i was changing was the formatting. Which I guess I did, estentially and 2)I didn't know until you told me.

churley5
11-13-2012, 03:40 PM
Ok I think I have this working now, with all the right formating except for one place.

I need to do two things and I think I can call this thing done.

1. I need to format the Plant Profit as currency and I'm not sure how to go about that.

2. I need the user form to be blank when it is called. I think I know the code to initialize the user form, I am assuming it would be something along the lines of `textbox.value = "". Correct me if I'm wrong. My question is where do I place the code for that?

I thought I would provide the new code.


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 = Format([SUMIF(Week1.Data[Processing Location],"Akron",Week1.Data[Gallons])] + gallons.Value, "Fixed")
processingtimeakron.Value = Format(akronprocessed.Value / [Constants!B5], "Fixed")
akrontotalhours.Value = Format(CDbl(processingtimeakron.Value) + CDbl(AkronOrders.Value), "Fixed")
akronutilization.Value = Format(CDbl(akrontotalhours.Value) / CDbl([Constants!B3]), "0.00%")


End If
End Sub

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

FtWayneOrders.Value = [COUNTIF(Week1.Data[Processing Location],"Fort Wayne")+1]
ftwayneprocessed.Value = Format([SUMIF(Week1.Data[Processing Location],"Fort Wayne",Week1.Data[Gallons])] + gallons.Value, "Fixed")
processingtimeftwayne.Value = Format(ftwayneprocessed.Value / [Constants!B5], "Fixed")
ftwaynetotalhours.Value = Format(CDbl(processingtimeftwayne.Value) + CDbl(FtWayneOrders.Value), "Fixed")
ftwayneutilization.Value = Format(CDbl(ftwaynetotalhours.Value) / CDbl([Constants!B3]), "0.00%")


End If
End Sub

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

RockfordOrders.Value = [COUNTIF(Week1.Data[Processing Location],"Rockford")+1]
rockfordprocessed.Value = Format([SUMIF(Week1.Data[Processing Location],"Rockford",Week1.Data[Gallons])] + gallons.Value, "Fixed")
processingtimerockford.Value = Format(rockfordprocessed.Value / [Constants!B5], "Fixed")
rockfordtotalhours.Value = Format(CDbl(processingtimerockford.Value) + CDbl(RockfordOrders.Value), "Fixed")
rockfordutilization.Value = Format(CDbl(rockfordtotalhours.Value) / CDbl([Constants!B3]), "0.00%")


End If
End Sub

Private Sub stlouisdistance_Change()
If IsNumeric(gallons.Value) And IsNumeric(stlouisdistance.Value) Then
stlouisorderprofit = profit(gallons.Value, stlouisdistance.Value)
SelectMaxProfit


StLouisOrders.Value = [COUNTIF(Week1.Data[Processing Location],"Saint Louis")+1]
stlouisprocessed.Value = Format([SUMIF(Week1.Data[Processing Location],"Saint Louis",Week1.Data[Gallons])] + gallons.Value, "Fixed")
processingtimestlouis.Value = Format(stlouisprocessed.Value / [Constants!B5], "Fixed")
stlouistotalhours.Value = Format(CDbl(processingtimestlouis.Value) + CDbl(StLouisOrders.Value), "Fixed")
stlouisutilization.Value = Format(CDbl(stlouistotalhours.Value) / CDbl([Constants!B3]), "0.00%")


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 = Format(a - b * c, "Currency")
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 = Format(Application.Max(f, r, s, a), "Currency")
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
'Make sheet 1 Active
Sheets(1).Activate
Unload Me
End Sub

p45cal
11-13-2012, 04:53 PM
Userform initialisation:
Private Sub UserForm_Initialize()
akronprofit.Value = Format([akron.profit], "Currency")
rockfordprofit.Value = Format([rockford.profit], "Currency")
ftwayneprofit.Value = Format([FTWayne.profit], "Currency")
stlouisprofit.Value = Format([stlouis.profit], "Currency")
End Sub
but set the Value and ControlSource properties to nothing for these controls.

I see you've created a utilization function but can's see where you're calling it from. If you want to call it from the sheet (in a formula) it should really be in a standard code-module, not a userform code-module. (Looking at it, it ignores the values of h and y you pass to it, so will give the same result regardless of the values of h and y! - I'm guessing it was just you experimenting?)

Finally, you need to run it and check that changing both the number of gallons and the distances on the form will cause adjustments to be made in all the places you expect there to be changes.

churley5
11-13-2012, 07:21 PM
Hah.... yeah I was just trying to understand the code a little better. I didn't use it because I decided to show the values on the user form. I just forgot to delete it.

I think everything is square. That is it seems to be working correctly. I just need to finish the formating and start on adding the functionailty (automatic distance calculation) for next weeks assignment.

I can't thank everyone enough. This is a very condensed class, and there is a lot of teach yourself stuff. I would have been in trouble if it wasn't for the help here.

churley5
11-15-2012, 03:31 PM
How would I add the order profit to the plant profit. Right now it doesn't consider the considered order. It only reports the plant profit as it stands currently.

Here is the current file.

p45cal
11-15-2012, 04:29 PM
untested, add the line in red:
Private Sub akrondistance_Change()
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 = Format([SUMIF(Week1.Data[Processing Location],"Akron",Week1.Data[Gallons])] + gallons.Value, "Fixed")
processingtimeakron.Value = Format(akronprocessed.Value / [Constants!B5], "Fixed")
akrontotalhours.Value = Format(CDbl(processingtimeakron.Value) + CDbl(AkronOrders.Value), "Fixed")
akronutilization.Value = Format(CDbl(akrontotalhours.Value) / CDbl([Constants!B3]), "0.00%")
akronprofit.Value = Format([akron.profit] + profit(gallons.Value, akrondistance.Value), "Currency")

End If
End Sub
do the same for all the other plants.

I note the gallons_change still only updates one field per plant; I suspect you would want to update several other fields for each plant too.

churley5
11-15-2012, 07:05 PM
I note the gallons_change still only updates one field per plant; I suspect you would want to update several other fields for each plant too.

I'm not certain I understand. The only plant metric I need is the Plant Profit. Everything else at the moment is for the order. Later on the capacity will need to be evaluated using solver but that is a couple of weeks from now.

Should I start a new thread for the part of this problem. I am sure I will have questions. Or would it be best to keep it here.

p45cal
11-16-2012, 01:37 AM
I'm not certain I understand. The only plant metric I need is the Plant Profit. Everything else at the moment is for the order. Later on the capacity will need to be evaluated using solver but that is a couple of weeks from now.

Should I start a new thread for the part of this problem. I am sure I will have questions. Or would it be best to keep it here. Well, if that's all you need, that's fine - but I do have to ask why you're calculating and displaying other values!

Try the following, just for Akron, on the file you attached most recently, without the red line adjustment. Fire up the userform, enter 333 for the gallons and 2 for the one way driving distance. I get the following blue values:
Amount processed:41833 (42166)
Processing time:69.72 (70.28)
Total operating hours:83.72 (84.28)
Plant Profit:7139.13
Order Profit:162.95 (279.50)
Plant Utilisation:69.77% (70.23%)

Now if I double the gallons to 666, the only thing that changes is the Order profit to:279.50.
Now if I change the driving distance to something else, only to change it straight back to 2 again, I get the figures in purple above. Shouldn't have those values changed when the gallons were changed?

I don't think you'll need to start a new thread.

churley5
11-17-2012, 09:03 AM
Well, if that's all you need, that's fine - but I do have to ask why you're calculating and displaying other values!

Try the following, just for Akron, on the file you attached most recently, without the red line adjustment. Fire up the userform, enter 333 for the gallons and 2 for the one way driving distance. I get the following blue values:
Amount processed:41833 (42166)
Processing time:69.72 (70.28)
Total operating hours:83.72 (84.28)
Plant Profit:7139.13
Order Profit:162.95 (279.50)
Plant Utilisation:69.77% (70.23%)

Now if I double the gallons to 666, the only thing that changes is the Order profit to:279.50.
Now if I change the driving distance to something else, only to change it straight back to 2 again, I get the figures in purple above. Shouldn't have those values changed when the gallons were changed?

I don't think you'll need to start a new thread.

Oh, I get what you are saying. Everything calculates or recalculates based on the distance being changed. You are saying if distance stays constant, and the gallons change. I had not thought about that. I was just assuming going from one cell to the next without editing the orginal. I could see how that would be useful. Would I just enter the same code into the gallons_change sub?

I have all the other values because its HW, and it helped me think through the logic a bit. They will be taken out for this rendition, and if memory serves it will just have the input box's, order/plant profit, and capacity utilization.

p45cal
11-17-2012, 09:48 AM
Would I just enter the same code into the gallons_change sub?Yes. Because the code is the same, you will be duplicating code. That's fine for now, but in future you'll find that managing duplicate ( or triplicate, etc.) code is a pain because if you make a change in one place, you'll have to find the duplicate code and make the change there too. Your tutor will probably tell you it's better to have only one version of that repeating code, and have the various event handlers call it. That way you only have to edit one bit of code.

churley5
11-17-2012, 09:41 PM
Yes. Because the code is the same, you will be duplicating code. That's fine for now, but in future you'll find that managing duplicate ( or triplicate, etc.) code is a pain because if you make a change in one place, you'll have to find the duplicate code and make the change there too. Your tutor will probably tell you it's better to have only one version of that repeating code, and have the various event handlers call it. That way you only have to edit one bit of code.

So how would you do that? Make them public? could you give me an example. I may play with that. I will have some time on my hands before the next assignment is due. I'm sure the teacher doesn't care how its coded. I honestly think she just checks for the functionality of the worksheet and doesn't look at the code. But I want to learn it, which is why I am here and not there asking her questions.

p45cal
11-18-2012, 03:38 AM
This is already happening in your workbook; You have a sub called SelectProfit containg some 9 lines of code. It's called from several other event handlers:akrondistance_Change, ftwaynedistance_Change, rockforddistance_Change, stlouisdistance_Change and, gallons_Change. So instead of having those 9 lines of code repeated in each event handler, that code only exists in one place making it easy to adjust.

To get the gallons_change to update the other fields we can cheat a bit here; instead of transferring that code to separate subs and have the event codes all call up the same subs, we can call up the distance_change events directly, as if they'd been triggered by changing the distance. So we could add the lines in red to the gallons_changeevent: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
akrondistance_Change
ftwaynedistance_Change
rockforddistance_Change
stlouisdistance_Change
End If
End Sub
A few points;
1. We find that selectMaxProfit is being called many times, both by the gallons_change event handler and by each of the distance_change handlers, so we can take it out of the gallons_change at least, which will reduce it to being called only 4 times when the gallons.value changes. We can live with that for the time being because it doesn't take long or use many resources.
2. Notice that the line:
ftwayneorderprofit = profit(gallons.Value, ftwaynedistance.Value)
is used by gallons_change AND the same line is executed in ftwaynedistance_Change. We don't need to have it run twice since no other relevant values change between them running. This means we can take it out of the gallons_change code. The same goes for the other three similar lines. Take 'em all out.
This leaves us with:Private Sub gallons_Change()
If IsNumeric(gallons.Value) Then
akrondistance_Change
ftwaynedistance_Change
rockforddistance_Change
stlouisdistance_Change
End If
End Sub That's looking clean, isn't it?
3. Just a minute… do we need the If IsNumeric(gallons.Value) Then check since it's checked in each of the called subs too? Well, we could take it out, but then we'd be calling 4 subs which might not need calling at all; it's a matter of preference - as the code in the called subs stands at the moment.
4. Just an aside; that last comment 'as the code in the called subs stands at the moment': There is nothing to cater for blanking out values in textboxes if the user changes gallons.value to something that is NOT numeric. If there were, that code might be in each of the 4 called subs, in which case you'd want to call the 4 subs unconditionally on the gallons.value being numeric or not. One for the future.

ps. All of the above is UNTESTED.

churley5
11-19-2012, 09:14 AM
I'm working on this some today. I'm going to add the auto calculation for distance first, then I will post where i'm at. Then try to clean it up some.

I have a question though. I have made another module for another sheet in the workbook. It runs correctly if I assign it to a button and click it. However, when I try to call that module in the zipcode_change handler I get an expected procedure not module error.

Private Sub zipcode_Change()
'Run Zip2Zip calculation
Call ZIP2ZIP
'Import Distance Calculation
akrondistance = [Main!C2]
ftwaynedistance = [Main!C3]
rockforddistance = [Main!C4]
stlouisdistance = [Main!C5]
End Sub

Do I just need to run the code straight from the event handler instead of using it as its own macro?

p45cal
11-19-2012, 10:51 AM
That depends on the code in Zip2Zip and what it does.

churley5
11-19-2012, 01:09 PM
That depends on the code in Zip2Zip and what it does.

Here is the code:

[vba]Option Explicit
Sub calc()
Dim ws As Worksheet
Dim start As String
Dim dest As String
Dim miles As Double
Dim i As Integer
Sheets("Main").Select
With Range("A2")
For i = 0 To 3
start = .Offset(i, 0).Value
dest = .Offset(i, 1).Value
For Each ws In ActiveWorkbook.Worksheets
Application.DisplayAlerts = False
If ws.Name = "Temp" Then ws.Delete
Application.DisplayAlerts = True
Next
Worksheets.Add().Name = "Temp"
Sheets("Main").Select
Application.DisplayAlerts = False


ActiveWorkbook.XmlImport

churley5
11-19-2012, 01:14 PM
Here is the code:

[vba]Option Explicit
Sub calc()
Dim ws As Worksheet
Dim start As String
Dim dest As String
Dim miles As Double
Dim i As Integer
Sheets("Main").Select
With Range("A2")
For i = 0 To 3
start = .Offset(i, 0).Value
dest = .Offset(i, 1).Value
For Each ws In ActiveWorkbook.Worksheets
Application.DisplayAlerts = False
If ws.Name = "Temp" Then ws.Delete
Application.DisplayAlerts = True
Next
Worksheets.Add().Name = "Temp"
Sheets("Main").Select
Application.DisplayAlerts = False


ActiveWorkbook.XmlImport

Ok I don't know what is going on, but the forum will only accept that much of the code. I have tried twice and it won't post the rest?

I'll attempt it again:


[VBA] ActiveWorkbook.XmlImport

churley5
11-19-2012, 01:23 PM
since I can't get the code to post, here is the file.

p45cal
11-19-2012, 04:58 PM
I'm pushed for time at the moment, so have a look at the (rough) code in the attached which seems to work.

churley5
11-19-2012, 08:07 PM
It does work, but I don't understand the code. Could you expain it?

churley5
11-19-2012, 08:08 PM
It does work, but I don't understand the code.

Could you explain it?

p45cal
11-20-2012, 05:10 AM
It does work, but I don't understand the code.
Could you explain it? You've probably realised now that you don't call a module, but a sub or a function within a module.
I've probably introduced too many things at once in the code in the last attachment - and one of them is a bit unconventional, where I call calc2 with three arguments:
calc2 zipcode.Value, AkronZip, Distance
Usually you supply a sub with values for it to do stuff with. Here, the two zip codes are supplied, but the Distance one is different in that it doesn't matter what value it holds when the sub is called, but I am interested in what value it holds when execution returns to the calling sub. I'm using the sub like a function, but functions aren't supposed to do things (like adding a sheet, changing cells, deleting sheets etc.) they're only supposed to return a value. Here I wanted to do stuff and return a value - so I used a sub (I've seen some functions do stuff - and I never sure when it's safe to use a function to do stuff, but using a sub takes away the doubt!).

You'll also see that I used zipcode_AfterUpdate rather than zipcode_Change, since the latter is triggered after every keypress in the textbox, whereas I only wanted the sub to be called with a complete zipcode.

The main bit to get your head around is this passing of arguments/parameters to subs and functions.

Now to another 'problem': "Could you explain it" !
I can, to some extent, but you probably realise that explaining stuff takes a heck of a lot more time than doing stuff. That's why you go on courses after all. So while it's probably not for your tutor to explain stuff that she hasn't touched on yet, you might be lucky if you asked her.

The best thing you could do to try to understand the code is to step through it, line by line, using F8 (there are also other things you can do here, like jump to bits of code where the cursor is with Ctrl + F8, skip over called subs (check out the Debug dropdown menu in the VBE). At the same time, have the Locals pane Open (Alt+V,S or use the View dropdown menu) and watch as variables have their values changed. You can also hover your mouse over a variable in the code and a little pop-up box sometimes appears showing you its value. Google you're already using, but can be a powerful friend. Lots of people have asked the same questions as you, and you'd be surprised how often you'll get useful stuff by using even quite specific (and quite a few) search terms (including of course, excel vba).

churley5
11-21-2012, 03:23 PM
Now to another 'problem': "Could you explain it" !
I can, to some extent, but you probably realise that explaining stuff takes a heck of a lot more time than doing stuff. That's why you go on courses after all. So while it's probably not for your tutor to explain stuff that she hasn't touched on yet, you might be lucky if you asked her.

I think ya did a fine job. I'm not looking to be spoon fed at all. Knowing what to look for in google is more than enough. I can usually figure stuff out once I know where to look for it at.

The teacher is less than what I call fluent in vba. So asking her usually doesn't get me very far, unless it is very basic code and done exactly like the book. This is her first time teaching VBA.

Now that the zipcode auto calculates and the distance go into the text box's on its own. I only need the three text box's left to be selectable. Is there a way to "lock out" the other text box's with using the enable feature in the properties? The reason I didn't want to use that route is that it makes the fon't almost the same color as the form and its hard to read. I see now that I should have used label's intead of text box's. But I will make do.

p45cal
11-21-2012, 06:27 PM
You could set their Locked property to true. They look the same, you can move the cursor around in the text box but you can't change it. Yep. labels would have been better.

churley5
11-25-2012, 08:00 PM
Ok. Been trying to wrap this thing up. I'm getting close to being done with this project.

I have cleaned it up a bit in general, and have the fields on the user form looking as best as I care to get them. I really wished I would have used labels, but I don't want to edit all the code. So I can live with it for now.

I'm adding some functionality to the "Managers Sheet". Just simple stuff like clearing the data, and deleting a specific order.

So I have made another form with a combobox. I need it to populate a list from whatever is in column one of my table with the actual data in it. I have tried several different ways (most stuff I found on google) of using the RowSource property. I can't get it to accept it. Is there a specific way it needs to be entered?

p45cal
11-26-2012, 05:09 AM
So I have made another form with a combobox. I need it to populate a list from whatever is in column one of my table with the actual data in it. I have tried several different ways (most stuff I found on google) of using the RowSource property. I can't get it to accept it. Is there a specific way it needs to be entered?If you type in the RowSource in the properties section in the vbe you just need something like
A2:A10
should do it, but this populates the combobox with that range of the active sheet, whichever one that happens to be at the time. You can specify the sheet name just as you would in a formula:
Sheet2!A2:A10
or if there's a space in the sheet name:
'Sheet 2'!A2:A10

You could also do this in the code when initialising the userform:
ComboBox1.RowSource = "'sheet 2'!A2:B10"
or even:
ComboBox1.RowSource = Sheets("sheet 2").Range("A2:B10").Address(external:=True)

Using the RowSource way of populating the combobox also allows you to have the column headers in the list box (set the combobox's ColumnHeads property to True) but be aware that the address you supply to RowSource does NOT include the headers, only the data, it looks above the data to find the headers.

Another way of populating the combobox is to use .List. Check it out; you won't be able to get the column headers though.

churley5
11-26-2012, 07:25 PM
Ok I think I got it populated correctly.

I have been trying to use the combo box to delete a record and it isn't going so hot. I looked up a bunch of stuff on google but none of it is working for my application. It keeps deleting it from the listbox itself I think. Really I'm assuming that I am implementing it incorrectly. I need it to delete the row based on the selection of the list box.

I have also added a group of labels to show some of the vital information about the selection. Could I use a traditional vlookup formula in the form of label1.value = [vlookup(x,x,x)] To fetch the correct data based on the selection in the combobox? If so what do you use as the variable for the combobox selection? Something like vlookup(combobox1.value,x,y)

Here is the newest version of the file.

p45cal
11-27-2012, 09:11 AM
Since you've used a range to populate that combobox, the combobox's .ListIndex is what you'll need to use to act on. If its value is -1, nothing's been selected yet. If something has been selected, then if the first item in the combobox has ben selected, the Listindex value is 0, if the second ite has been selected ten the listIndex value is 1 etc. You can use this value to identify the row in the table. This is safer than using VLookUp because there might be more than one similar value in the table, and VLookUp will only ever find the first one.
Try adding the following code to your UserForm2's code module:Private Sub ComboBox1_Change()
ComboBox1.RowSource = ComboBox1.RowSource ' just to refresh after a deleted row (also sets .ListIndex bact to -1 if the last item was deleted)
If ComboBox1.ListIndex <> -1 Then
Set xx = Sheets("Week1").ListObjects("Week1.Data").ListRows(ComboBox1.ListIndex + 1).Range
With xx
PlantLabel.Caption = .Cells(4)
GallonsLabel.Caption = .Cells(3)
ProfitLabel.Caption = .Cells(10)
End With
'Application.Goto xx 'selects the row if you want to show the user (or the developer!) which row is about to be deleted
End If
End Sub

Private Sub CommandButton1_Click() 'Delete Order button
If ComboBox1.ListIndex <> -1 Then
Sheets("Week1").ListObjects("Week1.Data").ListRows(ComboBox1.ListIndex + 1).Delete
End If
End Sub

ps. a ListObject is a Table.

churley5
12-01-2012, 02:06 PM
SO I have reached a point where I don't know where to start.

I had to build an optimization spreadsheet using the existing data. Which I have attached. I know how to do that.

However, it has now come time to integrate that functionality into the spreadsheet I have been working on. It only needs to optimize the orders that have been accepted and is user initiated. that is, the user hits a button at some point and solver optimizes profit based on the orders taken.

With the way my model is built, I'm not sure how to use solver, because the current data tab only includes the mileage of the assigned plant. This makes me think I need a new sheet, that shows all 4 plants mileages and profitability. The same as I have it in the Optimization HW sheet.

Thoughts?

p45cal
12-01-2012, 04:21 PM
SO I have reached a point where I don't know where to start.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 had to build an optimization spreadsheet using the existing data. Which I have attached. I know how to do that.I don't know how to do that. You'd better tell.



However, it has now come time to integrate that functionality into the spreadsheet I have been working on. It only needs to optimize the orders that have been accepted and is user initiated. that is, the user hits a button at some point and solver optimizes profit based on the orders taken. 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?



With the way my model is built, I'm not sure how to use solver,…nor am I!



because the current data tab only includes the mileage of the assigned plant.Your userform did all 4 mileage calculations, couldn't it be made to transfer them all to the sheet?



This makes me think I need a new sheet, that shows all 4 plants mileages and profitability. The same as I have it in the Optimization HW 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.

churley5
12-01-2012, 04:35 PM
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
12-01-2012, 06:48 PM
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

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

but it keeps hanging.

churley5
12-01-2012, 08:08 PM
This is what the code looks like.

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
'???