Originally Posted by
snb
Did you try my code in your really real sample ?
It creates all different price classes automatically (provided they reside in column 32).
Take your time to study the code.
The more you understand the better you can use it.
I did try now...
I am trying to understand a bit more, but not sure how it's going to work with the 1-14 like i said. I tried starting at column 10 which is the start of the first column 10 and doesn't work. I also tried with the end, and that also did not work..
run-time error '1004: application-defined or object defined error
doesnt highlight anything in the code
Sub M_snb()
Sheet1.Columns(5).Replace " ", " "
Sheet1.Columns(5).Replace "- ", "-"
Sheet1.Columns(5).Replace " -", "-"
sn = Sheet1.Cells(1).CurrentRegion
With CreateObject("scripting.dictionary")
For j = 3 To UBound(sn)
.Item(sn(j, 132)) = sn(j, 133)
Next
Sheet1.Cells(1, 10).Resize(, .Count) = .items
sq = .keys
.RemoveAll
sn = Sheet1.Cells(1).CurrentRegion
For j = 3 To UBound(sn)
c00 = sn(j, 3) & "_" & sn(j, 5)
If .exists(c00) Then
sp = .Item(c00)
Else
sp = Application.Index(sn, j, [transpose(row(1:133))])
End If
sp(Application.Match(sn(j, 132), sq, 0) + 133) = sn(j, 2)
.Item(c00) = sp
Next
Sheet2.Cells(1).Resize(, UBound(sn, 2)) = Sheet1.Cells(1).CurrentRegion.Rows(1).Value
Sheet2.Cells(2, 1).Resize(.Count, UBound(sn, 2)) = Application.Index(.items, 0, 0)
End With
End Sub
I also tried this, and switched them 1 for 1, and tried to see if they would add after column 133, still error
Sub M_snb()
Sheet1.Columns(5).Replace " ", " "
Sheet1.Columns(5).Replace "- ", "-"
Sheet1.Columns(5).Replace " -", "-"
sn = Sheet1.Cells(1).CurrentRegion
With CreateObject("scripting.dictionary")
For j = 3 To UBound(sn)
.Item(sn(j, 132)) = sn(j, 133)
Next
Sheet1.Cells(1, 134).Resize(, .Count) = .items
sq = .keys
.RemoveAll
sn = Sheet1.Cells(1).CurrentRegion
For j = 3 To UBound(sn)
c00 = sn(j, 3) & "_" & sn(j, 5)
If .exists(c00) Then
sp = .Item(c00)
Else
sp = Application.Index(sn, j, [transpose(row(1:133))])
End If
sp(Application.Match(sn(j, 132), sq, 0) + 133) = sn(j, 2)
.Item(c00) = sp
Next
Sheet2.Cells(1).Resize(, UBound(sn, 2)) = Sheet1.Cells(1).CurrentRegion.Rows(1).Value
Sheet2.Cells(2, 1).Resize(.Count, UBound(sn, 2)) = Application.Index(.items, 0, 0)
End With
End Sub