|
VB runtime errors...
Hey there!
So, I have an automated report that I get in Excel that reports various production milestones, alpha, beta, ship date, etc. Each product has up to 5 versions on the report, denoted by text in one of the columns. I want to combine all entries of a product that has consistent dates in 4 columns. This would leave only instances of the product with unique milestones, with the version columnconcatenating as needed. So, in essence of 7 columns I want 6 of them to match, and if they do, they should become one with all versions listed in column C. I've gotten help on this board, but the code that I have now gives me runtime errors. (I've gotten the 400 error, 1004, and another that says application or object based error.) Any ideas for what I can do? TIA! Here is my code: Sub Concat() Dim Iloop As Integer Dim Numrows As Integer Dim Counter As Integer Application.ScreenUpdating = False Numrows = Range("A65536").End(xlUp).Row Range("A1:G" & Numrows).Select Selection.Sort key1:=Range("A1"), Order1:=xlAscending, _ Key2:=Range("B1"), Order2:=xlAscending, Key3:=Range("C1"), _ Order3:=xlAscending, key4:=Range("D1"), Order4:=xlAscending, _ key5:=Range("E1"), Order5:=xlAscending, key6:=Range("F1"), Order6:=xlAscending, _ key7:=Range("G1"), Order7:=xlAscending, Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom For Iloop = Numrows To 2 Step -1 If Cells(Iloop, "A") = Cells(Iloop - 1, "A") & Cells(Iloop, "B") & Cells(Iloop - 1, "B") & Cells(Iloop, "D") = Cells(Iloop - 1, "D") _ & Cells(Iloop, "E") = Cells(Iloop - 1, "E") & Cells(Iloop, "F") = Cells(Iloop - 1, "F") & Cells(Iloop, "G") = Cells(Iloop - 1, "G") Then Cells(Iloop - 1, "C") = Cells(Iloop - 1, "C") & ", " & Cells(Iloop, "C") Rows(Iloop).Delete End If Next Iloop Range("A1").Select Application.ScreenUpdating = True End Sub |
Just like from the user interface, you only get 3 keys to sort by.
But you can do multiple sorts (manually or in code) to get the order you want. So use 3 sort commands. First sort by column G, then sort by D, E, F, and finally sort by A, B, and C. savvysam wrote: Hey there! So, I have an automated report that I get in Excel that reports various production milestones, alpha, beta, ship date, etc. Each product has up to 5 versions on the report, denoted by text in one of the columns. I want to combine all entries of a product that has consistent dates in 4 columns. This would leave only instances of the product with unique milestones, with the version columnconcatenating as needed. So, in essence of 7 columns I want 6 of them to match, and if they do, they should become one with all versions listed in column C. I've gotten help on this board, but the code that I have now gives me runtime errors. (I've gotten the 400 error, 1004, and another that says application or object based error.) Any ideas for what I can do? TIA! Here is my code: Sub Concat() Dim Iloop As Integer Dim Numrows As Integer Dim Counter As Integer Application.ScreenUpdating = False Numrows = Range("A65536").End(xlUp).Row Range("A1:G" & Numrows).Select Selection.Sort key1:=Range("A1"), Order1:=xlAscending, _ Key2:=Range("B1"), Order2:=xlAscending, Key3:=Range("C1"), _ Order3:=xlAscending, key4:=Range("D1"), Order4:=xlAscending, _ key5:=Range("E1"), Order5:=xlAscending, key6:=Range("F1"), Order6:=xlAscending, _ key7:=Range("G1"), Order7:=xlAscending, Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom For Iloop = Numrows To 2 Step -1 If Cells(Iloop, "A") = Cells(Iloop - 1, "A") & Cells(Iloop, "B") & Cells(Iloop - 1, "B") & Cells(Iloop, "D") = Cells(Iloop - 1, "D") _ & Cells(Iloop, "E") = Cells(Iloop - 1, "E") & Cells(Iloop, "F") = Cells(Iloop - 1, "F") & Cells(Iloop, "G") = Cells(Iloop - 1, "G") Then Cells(Iloop - 1, "C") = Cells(Iloop - 1, "C") & ", " & Cells(Iloop, "C") Rows(Iloop).Delete End If Next Iloop Range("A1").Select Application.ScreenUpdating = True End Sub -- Dave Peterson |
(This level of VB is a bit over my head, sorry.) Are you saying that the
sort keys I have in my code should be no more than 3? And if so, can you give me an example of a sort command for D, E, F? Thanks for your help!! S "Dave Peterson" wrote: Just like from the user interface, you only get 3 keys to sort by. But you can do multiple sorts (manually or in code) to get the order you want. So use 3 sort commands. First sort by column G, then sort by D, E, F, and finally sort by A, B, and C. savvysam wrote: Hey there! So, I have an automated report that I get in Excel that reports various production milestones, alpha, beta, ship date, etc. Each product has up to 5 versions on the report, denoted by text in one of the columns. I want to combine all entries of a product that has consistent dates in 4 columns. This would leave only instances of the product with unique milestones, with the version columnconcatenating as needed. So, in essence of 7 columns I want 6 of them to match, and if they do, they should become one with all versions listed in column C. I've gotten help on this board, but the code that I have now gives me runtime errors. (I've gotten the 400 error, 1004, and another that says application or object based error.) Any ideas for what I can do? TIA! Here is my code: Sub Concat() Dim Iloop As Integer Dim Numrows As Integer Dim Counter As Integer Application.ScreenUpdating = False Numrows = Range("A65536").End(xlUp).Row Range("A1:G" & Numrows).Select Selection.Sort key1:=Range("A1"), Order1:=xlAscending, _ Key2:=Range("B1"), Order2:=xlAscending, Key3:=Range("C1"), _ Order3:=xlAscending, key4:=Range("D1"), Order4:=xlAscending, _ key5:=Range("E1"), Order5:=xlAscending, key6:=Range("F1"), Order6:=xlAscending, _ key7:=Range("G1"), Order7:=xlAscending, Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom For Iloop = Numrows To 2 Step -1 If Cells(Iloop, "A") = Cells(Iloop - 1, "A") & Cells(Iloop, "B") & Cells(Iloop - 1, "B") & Cells(Iloop, "D") = Cells(Iloop - 1, "D") _ & Cells(Iloop, "E") = Cells(Iloop - 1, "E") & Cells(Iloop, "F") = Cells(Iloop - 1, "F") & Cells(Iloop, "G") = Cells(Iloop - 1, "G") Then Cells(Iloop - 1, "C") = Cells(Iloop - 1, "C") & ", " & Cells(Iloop, "C") Rows(Iloop).Delete End If Next Iloop Range("A1").Select Application.ScreenUpdating = True End Sub -- Dave Peterson |
That giant (incorrect) sort statement could be replaced with these 3 sort
statements: Selection.Sort _ key1:=Range("G1"), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom Selection.Sort _ key1:=Range("D1"), Order1:=xlAscending, _ key2:=Range("E1"), Order2:=xlAscending, _ key3:=Range("F1"), Order3:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom Selection.Sort _ key1:=Range("A1"), Order1:=xlAscending, _ key2:=Range("B1"), Order2:=xlAscending, _ key3:=Range("C1"), Order3:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom savvysam wrote: (This level of VB is a bit over my head, sorry.) Are you saying that the sort keys I have in my code should be no more than 3? And if so, can you give me an example of a sort command for D, E, F? Thanks for your help!! S "Dave Peterson" wrote: Just like from the user interface, you only get 3 keys to sort by. But you can do multiple sorts (manually or in code) to get the order you want. So use 3 sort commands. First sort by column G, then sort by D, E, F, and finally sort by A, B, and C. savvysam wrote: Hey there! So, I have an automated report that I get in Excel that reports various production milestones, alpha, beta, ship date, etc. Each product has up to 5 versions on the report, denoted by text in one of the columns. I want to combine all entries of a product that has consistent dates in 4 columns. This would leave only instances of the product with unique milestones, with the version columnconcatenating as needed. So, in essence of 7 columns I want 6 of them to match, and if they do, they should become one with all versions listed in column C. I've gotten help on this board, but the code that I have now gives me runtime errors. (I've gotten the 400 error, 1004, and another that says application or object based error.) Any ideas for what I can do? TIA! Here is my code: Sub Concat() Dim Iloop As Integer Dim Numrows As Integer Dim Counter As Integer Application.ScreenUpdating = False Numrows = Range("A65536").End(xlUp).Row Range("A1:G" & Numrows).Select Selection.Sort key1:=Range("A1"), Order1:=xlAscending, _ Key2:=Range("B1"), Order2:=xlAscending, Key3:=Range("C1"), _ Order3:=xlAscending, key4:=Range("D1"), Order4:=xlAscending, _ key5:=Range("E1"), Order5:=xlAscending, key6:=Range("F1"), Order6:=xlAscending, _ key7:=Range("G1"), Order7:=xlAscending, Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom For Iloop = Numrows To 2 Step -1 If Cells(Iloop, "A") = Cells(Iloop - 1, "A") & Cells(Iloop, "B") & Cells(Iloop - 1, "B") & Cells(Iloop, "D") = Cells(Iloop - 1, "D") _ & Cells(Iloop, "E") = Cells(Iloop - 1, "E") & Cells(Iloop, "F") = Cells(Iloop - 1, "F") & Cells(Iloop, "G") = Cells(Iloop - 1, "G") Then Cells(Iloop - 1, "C") = Cells(Iloop - 1, "C") & ", " & Cells(Iloop, "C") Rows(Iloop).Delete End If Next Iloop Range("A1").Select Application.ScreenUpdating = True End Sub -- Dave Peterson -- Dave Peterson |
Thanks, Dave! Hmm, after giving it a try it doesn't seem to be combining the
entries at all now. But, at least no errors, and it's definitely sorting properly... Here's what I've got now: Sub Concat() Dim Iloop As Integer Dim Numrows As Integer Dim Counter As Integer Application.ScreenUpdating = False Numrows = Range("A65536").End(xlUp).Row Range("A1:G" & Numrows).Select Selection.Sort _ key1:=Range("G1"), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom Selection.Sort _ key1:=Range("D1"), Order1:=xlAscending, _ key2:=Range("E1"), Order2:=xlAscending, _ key3:=Range("F1"), Order3:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom Selection.Sort _ key1:=Range("A1"), Order1:=xlAscending, _ key2:=Range("B1"), Order2:=xlAscending, _ key3:=Range("C1"), Order3:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom For Iloop = Numrows To 2 Step -1 If Cells(Iloop, "A") = Cells(Iloop - 1, "A") & Cells(Iloop, "B") = Cells(Iloop - 1, "B") & _ Cells(Iloop, "G") = Cells(Iloop - 1, "G") Then Cells(Iloop - 1, "C") = Cells(Iloop - 1, "C") & ", " & Cells(Iloop, "C") Rows(Iloop).Delete End If Next Iloop Range("A1").Select Application.ScreenUpdating = True End Sub "Dave Peterson" wrote: That giant (incorrect) sort statement could be replaced with these 3 sort statements: Selection.Sort _ key1:=Range("G1"), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom Selection.Sort _ key1:=Range("D1"), Order1:=xlAscending, _ key2:=Range("E1"), Order2:=xlAscending, _ key3:=Range("F1"), Order3:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom Selection.Sort _ key1:=Range("A1"), Order1:=xlAscending, _ key2:=Range("B1"), Order2:=xlAscending, _ key3:=Range("C1"), Order3:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom savvysam wrote: (This level of VB is a bit over my head, sorry.) Are you saying that the sort keys I have in my code should be no more than 3? And if so, can you give me an example of a sort command for D, E, F? Thanks for your help!! S "Dave Peterson" wrote: Just like from the user interface, you only get 3 keys to sort by. But you can do multiple sorts (manually or in code) to get the order you want. So use 3 sort commands. First sort by column G, then sort by D, E, F, and finally sort by A, B, and C. savvysam wrote: Hey there! So, I have an automated report that I get in Excel that reports various production milestones, alpha, beta, ship date, etc. Each product has up to 5 versions on the report, denoted by text in one of the columns. I want to combine all entries of a product that has consistent dates in 4 columns. This would leave only instances of the product with unique milestones, with the version columnconcatenating as needed. So, in essence of 7 columns I want 6 of them to match, and if they do, they should become one with all versions listed in column C. I've gotten help on this board, but the code that I have now gives me runtime errors. (I've gotten the 400 error, 1004, and another that says application or object based error.) Any ideas for what I can do? TIA! Here is my code: Sub Concat() Dim Iloop As Integer Dim Numrows As Integer Dim Counter As Integer Application.ScreenUpdating = False Numrows = Range("A65536").End(xlUp).Row Range("A1:G" & Numrows).Select Selection.Sort key1:=Range("A1"), Order1:=xlAscending, _ Key2:=Range("B1"), Order2:=xlAscending, Key3:=Range("C1"), _ Order3:=xlAscending, key4:=Range("D1"), Order4:=xlAscending, _ key5:=Range("E1"), Order5:=xlAscending, key6:=Range("F1"), Order6:=xlAscending, _ key7:=Range("G1"), Order7:=xlAscending, Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom For Iloop = Numrows To 2 Step -1 If Cells(Iloop, "A") = Cells(Iloop - 1, "A") & Cells(Iloop, "B") & Cells(Iloop - 1, "B") & Cells(Iloop, "D") = Cells(Iloop - 1, "D") _ & Cells(Iloop, "E") = Cells(Iloop - 1, "E") & Cells(Iloop, "F") = Cells(Iloop - 1, "F") & Cells(Iloop, "G") = Cells(Iloop - 1, "G") Then Cells(Iloop - 1, "C") = Cells(Iloop - 1, "C") & ", " & Cells(Iloop, "C") Rows(Iloop).Delete End If Next Iloop Range("A1").Select Application.ScreenUpdating = True End Sub -- Dave Peterson -- Dave Peterson |
I didn't look at the other portion. But VBA doesn't use & for And. You have to
use And. Maybe... Option Explicit Sub Concat() Dim Iloop As Long Dim Numrows As Long 'Dim Counter As Long Application.ScreenUpdating = False Numrows = Range("A65536").End(xlUp).Row Range("A1:G" & Numrows).Select Selection.Sort _ key1:=Range("G1"), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom Selection.Sort _ key1:=Range("D1"), Order1:=xlAscending, _ key2:=Range("E1"), Order2:=xlAscending, _ key3:=Range("F1"), Order3:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom Selection.Sort _ key1:=Range("A1"), Order1:=xlAscending, _ key2:=Range("B1"), Order2:=xlAscending, _ key3:=Range("C1"), Order3:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom For Iloop = Numrows To 2 Step -1 If Cells(Iloop, "A").Value = Cells(Iloop - 1, "A").Value _ And Cells(Iloop, "B").Value = Cells(Iloop - 1, "B").Value _ And Cells(Iloop, "G").Value = Cells(Iloop - 1, "G").Value Then Cells(Iloop - 1, "C").Value _ = Cells(Iloop - 1, "C").Value & ", " & Cells(Iloop, "C").Value Rows(Iloop).Delete End If Next Iloop Range("A1").Select Application.ScreenUpdating = True End Sub I changed Integer to Long--it actually speeds up the processing and I commented out the counter variable. I didn't see it used in this code. And I like to specify the property that I'm using (.value) even if it is the default. savvysam wrote: Thanks, Dave! Hmm, after giving it a try it doesn't seem to be combining the entries at all now. But, at least no errors, and it's definitely sorting properly... Here's what I've got now: Sub Concat() Dim Iloop As Integer Dim Numrows As Integer Dim Counter As Integer Application.ScreenUpdating = False Numrows = Range("A65536").End(xlUp).Row Range("A1:G" & Numrows).Select Selection.Sort _ key1:=Range("G1"), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom Selection.Sort _ key1:=Range("D1"), Order1:=xlAscending, _ key2:=Range("E1"), Order2:=xlAscending, _ key3:=Range("F1"), Order3:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom Selection.Sort _ key1:=Range("A1"), Order1:=xlAscending, _ key2:=Range("B1"), Order2:=xlAscending, _ key3:=Range("C1"), Order3:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom For Iloop = Numrows To 2 Step -1 If Cells(Iloop, "A") = Cells(Iloop - 1, "A") & Cells(Iloop, "B") = Cells(Iloop - 1, "B") & _ Cells(Iloop, "G") = Cells(Iloop - 1, "G") Then Cells(Iloop - 1, "C") = Cells(Iloop - 1, "C") & ", " & Cells(Iloop, "C") Rows(Iloop).Delete End If Next Iloop Range("A1").Select Application.ScreenUpdating = True End Sub "Dave Peterson" wrote: That giant (incorrect) sort statement could be replaced with these 3 sort statements: Selection.Sort _ key1:=Range("G1"), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom Selection.Sort _ key1:=Range("D1"), Order1:=xlAscending, _ key2:=Range("E1"), Order2:=xlAscending, _ key3:=Range("F1"), Order3:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom Selection.Sort _ key1:=Range("A1"), Order1:=xlAscending, _ key2:=Range("B1"), Order2:=xlAscending, _ key3:=Range("C1"), Order3:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom savvysam wrote: (This level of VB is a bit over my head, sorry.) Are you saying that the sort keys I have in my code should be no more than 3? And if so, can you give me an example of a sort command for D, E, F? Thanks for your help!! S "Dave Peterson" wrote: Just like from the user interface, you only get 3 keys to sort by. But you can do multiple sorts (manually or in code) to get the order you want. So use 3 sort commands. First sort by column G, then sort by D, E, F, and finally sort by A, B, and C. savvysam wrote: Hey there! So, I have an automated report that I get in Excel that reports various production milestones, alpha, beta, ship date, etc. Each product has up to 5 versions on the report, denoted by text in one of the columns. I want to combine all entries of a product that has consistent dates in 4 columns. This would leave only instances of the product with unique milestones, with the version columnconcatenating as needed. So, in essence of 7 columns I want 6 of them to match, and if they do, they should become one with all versions listed in column C. I've gotten help on this board, but the code that I have now gives me runtime errors. (I've gotten the 400 error, 1004, and another that says application or object based error.) Any ideas for what I can do? TIA! Here is my code: Sub Concat() Dim Iloop As Integer Dim Numrows As Integer Dim Counter As Integer Application.ScreenUpdating = False Numrows = Range("A65536").End(xlUp).Row Range("A1:G" & Numrows).Select Selection.Sort key1:=Range("A1"), Order1:=xlAscending, _ Key2:=Range("B1"), Order2:=xlAscending, Key3:=Range("C1"), _ Order3:=xlAscending, key4:=Range("D1"), Order4:=xlAscending, _ key5:=Range("E1"), Order5:=xlAscending, key6:=Range("F1"), Order6:=xlAscending, _ key7:=Range("G1"), Order7:=xlAscending, Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom For Iloop = Numrows To 2 Step -1 If Cells(Iloop, "A") = Cells(Iloop - 1, "A") & Cells(Iloop, "B") & Cells(Iloop - 1, "B") & Cells(Iloop, "D") = Cells(Iloop - 1, "D") _ & Cells(Iloop, "E") = Cells(Iloop - 1, "E") & Cells(Iloop, "F") = Cells(Iloop - 1, "F") & Cells(Iloop, "G") = Cells(Iloop - 1, "G") Then Cells(Iloop - 1, "C") = Cells(Iloop - 1, "C") & ", " & Cells(Iloop, "C") Rows(Iloop).Delete End If Next Iloop Range("A1").Select Application.ScreenUpdating = True End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
YYYAAAAY! That worked. Thanks so much for all your help!! :-)
S "Dave Peterson" wrote: I didn't look at the other portion. But VBA doesn't use & for And. You have to use And. Maybe... Option Explicit Sub Concat() Dim Iloop As Long Dim Numrows As Long 'Dim Counter As Long Application.ScreenUpdating = False Numrows = Range("A65536").End(xlUp).Row Range("A1:G" & Numrows).Select Selection.Sort _ key1:=Range("G1"), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom Selection.Sort _ key1:=Range("D1"), Order1:=xlAscending, _ key2:=Range("E1"), Order2:=xlAscending, _ key3:=Range("F1"), Order3:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom Selection.Sort _ key1:=Range("A1"), Order1:=xlAscending, _ key2:=Range("B1"), Order2:=xlAscending, _ key3:=Range("C1"), Order3:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom For Iloop = Numrows To 2 Step -1 If Cells(Iloop, "A").Value = Cells(Iloop - 1, "A").Value _ And Cells(Iloop, "B").Value = Cells(Iloop - 1, "B").Value _ And Cells(Iloop, "G").Value = Cells(Iloop - 1, "G").Value Then Cells(Iloop - 1, "C").Value _ = Cells(Iloop - 1, "C").Value & ", " & Cells(Iloop, "C").Value Rows(Iloop).Delete End If Next Iloop Range("A1").Select Application.ScreenUpdating = True End Sub I changed Integer to Long--it actually speeds up the processing and I commented out the counter variable. I didn't see it used in this code. And I like to specify the property that I'm using (.value) even if it is the default. savvysam wrote: Thanks, Dave! Hmm, after giving it a try it doesn't seem to be combining the entries at all now. But, at least no errors, and it's definitely sorting properly... Here's what I've got now: Sub Concat() Dim Iloop As Integer Dim Numrows As Integer Dim Counter As Integer Application.ScreenUpdating = False Numrows = Range("A65536").End(xlUp).Row Range("A1:G" & Numrows).Select Selection.Sort _ key1:=Range("G1"), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom Selection.Sort _ key1:=Range("D1"), Order1:=xlAscending, _ key2:=Range("E1"), Order2:=xlAscending, _ key3:=Range("F1"), Order3:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom Selection.Sort _ key1:=Range("A1"), Order1:=xlAscending, _ key2:=Range("B1"), Order2:=xlAscending, _ key3:=Range("C1"), Order3:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom For Iloop = Numrows To 2 Step -1 If Cells(Iloop, "A") = Cells(Iloop - 1, "A") & Cells(Iloop, "B") = Cells(Iloop - 1, "B") & _ Cells(Iloop, "G") = Cells(Iloop - 1, "G") Then Cells(Iloop - 1, "C") = Cells(Iloop - 1, "C") & ", " & Cells(Iloop, "C") Rows(Iloop).Delete End If Next Iloop Range("A1").Select Application.ScreenUpdating = True End Sub "Dave Peterson" wrote: That giant (incorrect) sort statement could be replaced with these 3 sort statements: Selection.Sort _ key1:=Range("G1"), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom Selection.Sort _ key1:=Range("D1"), Order1:=xlAscending, _ key2:=Range("E1"), Order2:=xlAscending, _ key3:=Range("F1"), Order3:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom Selection.Sort _ key1:=Range("A1"), Order1:=xlAscending, _ key2:=Range("B1"), Order2:=xlAscending, _ key3:=Range("C1"), Order3:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom savvysam wrote: (This level of VB is a bit over my head, sorry.) Are you saying that the sort keys I have in my code should be no more than 3? And if so, can you give me an example of a sort command for D, E, F? Thanks for your help!! S "Dave Peterson" wrote: Just like from the user interface, you only get 3 keys to sort by. But you can do multiple sorts (manually or in code) to get the order you want. So use 3 sort commands. First sort by column G, then sort by D, E, F, and finally sort by A, B, and C. savvysam wrote: Hey there! So, I have an automated report that I get in Excel that reports various production milestones, alpha, beta, ship date, etc. Each product has up to 5 versions on the report, denoted by text in one of the columns. I want to combine all entries of a product that has consistent dates in 4 columns. This would leave only instances of the product with unique milestones, with the version columnconcatenating as needed. So, in essence of 7 columns I want 6 of them to match, and if they do, they should become one with all versions listed in column C. I've gotten help on this board, but the code that I have now gives me runtime errors. (I've gotten the 400 error, 1004, and another that says application or object based error.) Any ideas for what I can do? TIA! Here is my code: Sub Concat() Dim Iloop As Integer Dim Numrows As Integer Dim Counter As Integer Application.ScreenUpdating = False Numrows = Range("A65536").End(xlUp).Row Range("A1:G" & Numrows).Select Selection.Sort key1:=Range("A1"), Order1:=xlAscending, _ Key2:=Range("B1"), Order2:=xlAscending, Key3:=Range("C1"), _ Order3:=xlAscending, key4:=Range("D1"), Order4:=xlAscending, _ key5:=Range("E1"), Order5:=xlAscending, key6:=Range("F1"), Order6:=xlAscending, _ key7:=Range("G1"), Order7:=xlAscending, Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom For Iloop = Numrows To 2 Step -1 If Cells(Iloop, "A") = Cells(Iloop - 1, "A") & Cells(Iloop, "B") & Cells(Iloop - 1, "B") & Cells(Iloop, "D") = Cells(Iloop - 1, "D") _ & Cells(Iloop, "E") = Cells(Iloop - 1, "E") & Cells(Iloop, "F") = Cells(Iloop - 1, "F") & Cells(Iloop, "G") = Cells(Iloop - 1, "G") Then Cells(Iloop - 1, "C") = Cells(Iloop - 1, "C") & ", " & Cells(Iloop, "C") Rows(Iloop).Delete End If Next Iloop Range("A1").Select Application.ScreenUpdating = True End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Woohoo!!!
<<snipped |
| All times are GMT +1. The time now is 03:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com