Consulting

Page 3 of 4 FirstFirst 1 2 3 4 LastLast
Results 41 to 60 of 63

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

  1. #41
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by churley5
    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.
    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.

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

  3. #43
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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:[vba]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
    [/vba] 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:[vba]Private Sub gallons_Change()
    If IsNumeric(gallons.Value) Then
    akrondistance_Change
    ftwaynedistance_Change
    rockforddistance_Change
    stlouisdistance_Change
    End If
    End Sub[/vba] 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.
    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.

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

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

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

  5. #45
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    That depends on the code in Zip2Zip and what it does.
    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. #46
    VBAX Regular
    Joined
    Nov 2012
    Posts
    37
    Location
    Quote Originally Posted by p45cal
    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

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

  8. #48
    VBAX Regular
    Joined
    Nov 2012
    Posts
    37
    Location
    since I can't get the code to post, here is the file.
    Attached Files Attached Files

  9. #49
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    I'm pushed for time at the moment, so have a look at the (rough) code in the attached which seems to work.
    Attached Files Attached Files
    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. #50
    VBAX Regular
    Joined
    Nov 2012
    Posts
    37
    Location
    It does work, but I don't understand the code. Could you expain it?

  11. #51
    VBAX Regular
    Joined
    Nov 2012
    Posts
    37
    Location
    It does work, but I don't understand the code.

    Could you explain it?

  12. #52
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by churley5
    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).
    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.

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

  14. #54
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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.
    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. #55
    VBAX Regular
    Joined
    Nov 2012
    Posts
    37
    Location
    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?

  16. #56
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by churley5
    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.
    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.

  17. #57
    VBAX Regular
    Joined
    Nov 2012
    Posts
    37
    Location
    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 [VBA]label1.value = [vlookup(x,x,x)][/VBA] 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.
    Attached Files Attached Files

  18. #58
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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:[VBA]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
    [/VBA]
    ps. a ListObject is a Table.
    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.

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

  20. #60
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by churley5
    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?


    Quote Originally Posted by churley5
    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.


    Quote Originally Posted by churley5
    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?


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


    Quote Originally Posted by churley5
    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?


    Quote Originally Posted by churley5
    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.
    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.

Posting Permissions

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