Consulting

Page 1 of 4 1 2 3 ... LastLast
Results 1 to 20 of 63

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

  1. #1
    VBAX Regular
    Joined
    Nov 2012
    Posts
    37
    Location

    I can't get the correct Rows to delete

    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.
    Attached Files Attached Files

  2. #2
    VBAX Mentor
    Joined
    Jul 2012
    Posts
    398
    Location
    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 ?

  3. #3
    VBAX Regular
    Joined
    Nov 2012
    Posts
    37
    Location
    Yes it is the command button 2.

    Yes the akron_checked is a radio button.

    I have gotten closer currently the VBA is this

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


    [/VBA]

  4. #4
    VBAX Mentor
    Joined
    Jul 2012
    Posts
    398
    Location
    Quote Originally Posted by churley5
    Yes it is the command button 2.

    Yes the akron_checked is a radio button.
    you are wrong, see attached
    Attached Images Attached Images

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

    [vba]
    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

    [/vba]
    Last edited by churley5; 11-10-2012 at 01:15 PM.

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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.
    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.

  7. #7
    VBAX Mentor Teeroy's Avatar
    Joined
    Apr 2012
    Location
    Sydney, Australia
    Posts
    414
    Location
    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.

    [vba]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
    [/vba]
    _________________________________________________________________________
    "In theory there is no difference between theory and practice. In practice there is." - Chuck Reid

    Any day you learn something new is a day not wasted.

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

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

    [vba]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
    [/vba]
    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>
    [VBA]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"), ",") [/VBA]

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

  10. #10
    VBAX Mentor Teeroy's Avatar
    Joined
    Apr 2012
    Location
    Sydney, Australia
    Posts
    414
    Location
    Quote Originally Posted by churley5
    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)

    Quote Originally Posted by churley5
    Second is I'm not sure what this code does>
    [vba]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"), ",") [/vba]
    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.
    _________________________________________________________________________
    "In theory there is no difference between theory and practice. In practice there is." - Chuck Reid

    Any day you learn something new is a day not wasted.

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

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

    End Sub[/VBA]

  12. #12
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    sure, you could use
    [VBA]AkronOrders.Value = Application.WorksheetFunction.CountIf(Sheets("Week1").Range("D"), "Akron") + 1[/VBA]but 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:[VBA]AkronOrders.Value = [COUNTIF(Week1.Data[Processing Location],"Akron")+1][/VBA]
    Last edited by p45cal; 11-11-2012 at 11:29 AM.
    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. #13
    VBAX Regular
    Joined
    Nov 2012
    Posts
    37
    Location
    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.

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

    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.

  14. #14
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    this:
    [vba]processingtimeakron.Value = akronprocessed.Value / [Constants B5] [/vba] should be:
    [vba]processingtimeakron.Value = akronprocessed.Value / [Constants!B5][/vba]
    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
    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. #15
    VBAX Regular
    Joined
    Nov 2012
    Posts
    37
    Location
    Quote Originally Posted by p45cal
    this:
    [vba]processingtimeakron.Value = akronprocessed.Value / [Constants B5] [/vba] should be:
    [vba]processingtimeakron.Value = akronprocessed.Value / [Constants!B5][/vba]
    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.

  16. #16
    VBAX Regular
    Joined
    Nov 2012
    Posts
    37
    Location
    Quote Originally Posted by p45cal
    this:
    [vba]processingtimeakron.Value = akronprocessed.Value / [Constants B5] [/vba] should be:
    [vba]processingtimeakron.Value = akronprocessed.Value / [Constants!B5][/vba]
    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:
    [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[/VBA]

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

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

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

  19. #19
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Try moving a closing square bracket:
    [vba]akronprocessed.Value = [SUMIF(Week1.Data[Processing Location],"Akron",Week1.Data[Gallons])] + gallons.value[/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.

  20. #20
    VBAX Regular
    Joined
    Nov 2012
    Posts
    37
    Location
    Quote Originally Posted by p45cal
    Try moving a closing square bracket:
    [vba]akronprocessed.Value = [SUMIF(Week1.Data[Processing Location],"Akron",Week1.Data[Gallons])] + gallons.value[/vba]

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

Posting Permissions

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