엑셀 VBA #127 / Vlookup시리즈3_배열+Dictionary활용 [VBA]

엑셀 VBA #127 / Vlookup시리즈3_배열+Dictionary활용 [VBA]

Sub Vlookup기능실현_New_Scripting_Dictionary_배열231228()
      
      Dim dict As New Scripting.Dictionary
      Dim rngS, rngF
      Dim i As Long
      Dim arr()
      
      rngS = Sheet1.Range("a2", Sheet1.Cells(Rows.Count, "b").End(3)) 'rngS배열방 지정
      rngF = Sheet2.Range("a2", Sheet2.Cells(Rows.Count, "a").End(3)) 'rngT배열방 지정
      
      For i = 1 To UBound(rngS, 1)
            If dict.Exists(rngS(i, 1)) Then
            Else
                  dict.Add rngS(i, 1), rngS(i, 2)
                  'rngS(i,1)은 Key값, rngS(i,2)는 Item값임!!!!!
                  'cells(i,1)..cells(i,2)가 아님에 주의
            End If
      Next

      ReDim arr(1 To UBound(rngF, 1), 1 To 1)
      For i = 1 To UBound(rngF, 1)
            If dict.Exists(rngF(i, 1)) Then
                  arr(i, 1) = dict(rngF(i, 1))
                  ' "rngF(i,1)"은 Key값이고, 
                  "dict(rngF(i,1))"은 그 Key값에 해당하는 Item의 값임!!!!!!!
            Else
            End If
      Next
      
      Sheet2.Range("e2").Resize(UBound(arr, 1), 1) = arr

End Sub

vba#127.xlsm

0.31MB

엑셀 VBA #118 / Dictionary 개체_기본 [VBA]
엑셀 VBA #119 / Dictionary 개체_실무 [VBA]



https://www.youtube.com/watch?v=I4iyh9tF5kc&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=129
ㅁㅁ