엑셀 VBA #26 / For Each~Next_2 [VBA] - New Collection

엑셀 VBA #26 / For Each~Next_2 [VBA] - New Collection

On Error Resume Next
For Each c In rng
      dc.Add Trim(c), CStr(Trim(c))
Next
On Error GoTo 0

 

 

 

For i = 1 To dc.Count
      Cells(i + 11, "A") = dc(i)
      Cells(i + 11, "B") = Application.SumIf(rc, dc(i), rs)

 

Sub For_Each_Next_and_New_Collection_고유값합계()

Dim dc As New Collection
Dim rng As Range
Dim c As Range
Dim i As Long
Dim rc As Range, rs As Range

Set rng = Range("A2:A" & Range("A2").CurrentRegion.Rows.Count)
'=Set rng = Range("A2", Cells(Rows.Count, "A").End(xlUp))


On Error Resume Next
For Each c In rng
      dc.Add Trim(c), CStr(Trim(c))
Next
On Error GoTo 0

Range("A11") = "고유목록별 합계"

Set rc = Range("A2:A9")
Set rs = Range("B2:B9")

For i = 1 To dc.Count
      Cells(i + 11, "A") = dc(i)
      Cells(i + 11, "B") = Application.SumIf(rc, dc(i), rs)
      
Next
End Sub

 

vba#26.xlsm

0.02MB

 

핵심정리

On Error Resume Next
For Each c In rng
      dc.Add Trim(c), CStr(Trim(c))
Next
On Error GoTo 0

 

https://www.youtube.com/watch?v=BC_O6sVx4SA&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=26

How to use VBA Collections in Excel