idnoidno
06-28-2017, 08:09 PM
19625
I put two columns of data(the first and second columns) into a set of variables(the three column), and then use the scripting.dictionary method to read the variables to generate the dictionary.keys, and then use the split function to separate the variables.But I have some question want to ask,
1) How to separate the variables into their original patterns, and then write them in columns 5 and 6
2)Why
sub t4 can work
mykey = d.keys
For i = 0 To UBound(mykey)
brr = Split(mykey(i), "_")
Cells(i + 1, 6) = brr(0)
Cells(i + 1, 7) = brr(1)
Next i
,but subt5 cannot
For i = 0 To d.Count
brr = Split(d.keys, "_")
Cells(i + 1, 6) = brr(0)
Cells(i + 1, 7) = brr(1)
Sub t4()
Dim d As Object
Dim i As Long
Dim arr, brr, s, mykey
Set d = CreateObject("scripting.dictionary")
arr = [a1].CurrentRegion
For i = 2 To UBound(arr)
s = arr(i, 1) & "_" & arr(i, 2)
If Not d.exists(s) Then
d(s) = arr(i, 3)
Else
d(s) = d(s) + arr(i, 3)
End If
Next i
mykey = d.keys
For i = 0 To UBound(mykey)
brr = Split(mykey(i), "_")
Cells(i + 1, 6) = brr(0)
Cells(i + 1, 7) = brr(1)
Next i
[h1].Resize(d.Count) = Application.Transpose(d.items)
Set d = Nothing
End Sub
Sub t5()
Dim d As Object
Dim i As Long
Dim arr, brr, s, mykey
Set d = CreateObject("scripting.dictionary")
arr = [a1].CurrentRegion
For i = 2 To UBound(arr)
s = arr(i, 1) & "_" & arr(i, 2)
If Not d.exists(s) Then
d(s) = arr(i, 3)
Else
d(s) = d(s) + arr(i, 3)
End If
Next i
For i = 0 To d.Count
brr = Split(d.keys, "_")
Cells(i + 1, 6) = brr(0)
Cells(i + 1, 7) = brr(1)
Next i
[h1].Resize(d.Count) = Application.Transpose(d.items)
Set d = Nothing
End Sub
I put two columns of data(the first and second columns) into a set of variables(the three column), and then use the scripting.dictionary method to read the variables to generate the dictionary.keys, and then use the split function to separate the variables.But I have some question want to ask,
1) How to separate the variables into their original patterns, and then write them in columns 5 and 6
2)Why
sub t4 can work
mykey = d.keys
For i = 0 To UBound(mykey)
brr = Split(mykey(i), "_")
Cells(i + 1, 6) = brr(0)
Cells(i + 1, 7) = brr(1)
Next i
,but subt5 cannot
For i = 0 To d.Count
brr = Split(d.keys, "_")
Cells(i + 1, 6) = brr(0)
Cells(i + 1, 7) = brr(1)
Sub t4()
Dim d As Object
Dim i As Long
Dim arr, brr, s, mykey
Set d = CreateObject("scripting.dictionary")
arr = [a1].CurrentRegion
For i = 2 To UBound(arr)
s = arr(i, 1) & "_" & arr(i, 2)
If Not d.exists(s) Then
d(s) = arr(i, 3)
Else
d(s) = d(s) + arr(i, 3)
End If
Next i
mykey = d.keys
For i = 0 To UBound(mykey)
brr = Split(mykey(i), "_")
Cells(i + 1, 6) = brr(0)
Cells(i + 1, 7) = brr(1)
Next i
[h1].Resize(d.Count) = Application.Transpose(d.items)
Set d = Nothing
End Sub
Sub t5()
Dim d As Object
Dim i As Long
Dim arr, brr, s, mykey
Set d = CreateObject("scripting.dictionary")
arr = [a1].CurrentRegion
For i = 2 To UBound(arr)
s = arr(i, 1) & "_" & arr(i, 2)
If Not d.exists(s) Then
d(s) = arr(i, 3)
Else
d(s) = d(s) + arr(i, 3)
End If
Next i
For i = 0 To d.Count
brr = Split(d.keys, "_")
Cells(i + 1, 6) = brr(0)
Cells(i + 1, 7) = brr(1)
Next i
[h1].Resize(d.Count) = Application.Transpose(d.items)
Set d = Nothing
End Sub