Consulting

Page 2 of 4 FirstFirst 1 2 3 4 LastLast
Results 21 to 40 of 63

Thread: I can't get the correct Rows to delete

  1. #21
    VBAX Regular
    Joined
    Nov 2012
    Posts
    37
    Location
    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]
    Attached Files Attached Files

  2. #22
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    same error, different line:
    processingtimeakron.Value = akronprocessed.Value / [Constants!B5]
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #23
    VBAX Regular
    Joined
    Nov 2012
    Posts
    37
    Location
    Quote Originally Posted by p45cal
    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.

  4. #24
    VBAX Regular
    Joined
    Nov 2012
    Posts
    37
    Location
    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?

  5. #25
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #26
    VBAX Regular
    Joined
    Nov 2012
    Posts
    37
    Location
    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.

    [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 = 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[/VBA]

  7. #27
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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:
    [vba]x=akrontotalhours.Value
    y=[Constants!B3]
    z=x/y
    zz=format(z,"fixed")[/vba]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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #28
    VBAX Regular
    Joined
    Nov 2012
    Posts
    37
    Location
    [VBA]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%")[/VBA]

    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.

  9. #29
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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:
    Quote Originally Posted by churley5
    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:
    [vba]akrontotalhours.Value = Format(CDbl(processingtimeakron.Value) + CDbl(AkronOrders.Value), "Fixed")[/vba] 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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  10. #30
    VBAX Regular
    Joined
    Nov 2012
    Posts
    37
    Location
    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

  11. #31
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by churley5
    I have no idea what went wrong.
    ??!!
    I told you:
    Quote Originally Posted by p45cal
    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!:
    Quote Originally Posted by p45cal
    The result of the format function is a string so excel might not always recognise it as a numeric value.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  12. #32
    VBAX Regular
    Joined
    Nov 2012
    Posts
    37
    Location
    Quote Originally Posted by p45cal
    ??!!
    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.

  13. #33
    VBAX Regular
    Joined
    Nov 2012
    Posts
    37
    Location
    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.

    [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 = 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

    [/VBA]

  14. #34
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Userform initialisation:
    [VBA]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
    [/VBA]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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  15. #35
    VBAX Regular
    Joined
    Nov 2012
    Posts
    37
    Location
    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.

  16. #36
    VBAX Regular
    Joined
    Nov 2012
    Posts
    37
    Location
    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.
    Attached Files Attached Files

  17. #37
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    untested, add the line in red:
    [vba]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
    [/vba]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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  18. #38
    VBAX Regular
    Joined
    Nov 2012
    Posts
    37
    Location
    Quote Originally Posted by p45cal

    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.

  19. #39
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by churley5
    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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  20. #40
    VBAX Regular
    Joined
    Nov 2012
    Posts
    37
    Location
    Quote Originally Posted by p45cal
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •