VB runtime errors...

S

savvysam

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
 
D

Dave Peterson

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.
 
S

savvysam

(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
 
D

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
 
S

savvysam

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
 
D

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.


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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Delete Duplicate Rows Macro Question 1
VB Error, combining rows if... 1
Sub Concatenate runtime errors 0
Problem using INDEX to select items in listbox 3
Adding Zeros in front of numbers in cells using Excel 2003 14
counif by column2 & column 4 ? 3
Time Stamp for specific macro.. 7
active cell issue... 5

Top