vba 내선번호 배열 드래그앤드롭

ListBox1 ListBox2 ListBox3 ListBox4

내보내기

Private Sub CommandButton1_Click()
Dim i As Long
    Sheets("표").Range("A2", Cells(Rows.Count, "A").End(xlUp)).Resize(, 3).ClearContents
    Sheets("표").Range("D2", Cells(Rows.Count, "D").End(xlUp)).Resize(, 3).ClearContents
    Sheets("표").Range("G2", Cells(Rows.Count, "G").End(xlUp)).Resize(, 3).ClearContents
    Sheets("표").Range("J2", Cells(Rows.Count, "J").End(xlUp)).Resize(, 3).ClearContents
    
    For i = 0 To ListBox1.ListCount - 1
        Sheets("표").Range("A2").Offset(i) = Me.ListBox1.List(i, 0)
    Next
    For i = 0 To ListBox2.ListCount - 1
        Sheets("표").Range("D2").Offset(i) = Me.ListBox2.List(i, 0)
    Next
    For i = 0 To ListBox3.ListCount - 1
        Sheets("표").Range("G2").Offset(i) = Me.ListBox3.List(i, 0)
    Next
    For i = 0 To ListBox4.ListCount - 1
        Sheets("표").Range("J2").Offset(i) = Me.ListBox4.List(i, 0)
    Next
    Call 내선번호
    
End Sub

Private Sub UserForm_Initialize()
    ' Listbox1 초기화: Excel 시트 데이터를 Listbox1에 로드
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("표") ' 시트 이름을 변경하세요.
    
    '@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    Dim lastRow_A As Long
    lastRow_A = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    Dim cell_A As Range
    Dim i_A As Long
    
    For Each cell_A In ws.Range("A2:A" & lastRow_A)
        ListBox1.AddItem cell_A.Value
    Next cell_A
    '@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    Dim lastRow_D As Long
    lastRow_D = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
    
    Dim cell_D As Range
    Dim i_D As Long
    
    For Each cell_D In ws.Range("D2:D" & lastRow_D)
        ListBox2.AddItem cell_D.Value
    Next cell_D
    '@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    Dim lastRow_G As Long
    lastRow_G = ws.Cells(ws.Rows.Count, "G").End(xlUp).Row

    Dim cell_G As Range
    Dim i_G As Long

    For Each cell_G In ws.Range("G2:G" & lastRow_G)
        ListBox3.AddItem cell_G.Value
    Next cell_G
    '@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    Dim lastRow_J As Long
    lastRow_J = ws.Cells(ws.Rows.Count, "J").End(xlUp).Row
    
    Dim cell_J As Range
    Dim i_J As Long
    
    For Each cell_J In ws.Range("J2:J" & lastRow_J)
        ListBox4.AddItem cell_J.Value
    Next cell_J
    '@@@@@@@@@@@@@@@@@@@@@@@@@@@@
'    Dim i As Integer
'    For i = 1 To 10
'        ListBox1.AddItem i
'        ListBox2.AddItem i
'        ListBox3.AddItem i
'    Next
    Set oDragDropForm = New clDragDropForm
    With oDragDropForm
        Set .DragDropForm = Me
        .GetListboxes
        .DropEffect = MoveItem
        .DropType = CursorPosition
        .AllowDropInSameListbox = True
    End With
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    oDragDropForm.TerminateDragDrop
End Sub

close

Private Sub btnClose_Click()
    Unload Me
End Sub

폼 fmListboxDragDropDemo3

Sub DragDropDemo3()
    fmListboxDragDropDemo3.Show
End Sub

Sub 내선번호()
Dim rngS As Variant
Dim rngFA As Variant
Dim rngFD As Variant
Dim rngFG As Variant
Dim rngFJ As Variant
Dim i As Long
Dim j As Long
Dim arrA(), arrD(), arrG(), arrJ()
'Sheets("표").Range("B2", Sheets("표").Cells(Rows.Count, "C").End(xlUp)).ClearContents
'Sheets("표").Range("E2", Sheets("표").Cells(Rows.Count, "F").End(xlUp)).ClearContents
'Sheets("표").Range("H2", Sheets("표").Cells(Rows.Count, "I").End(xlUp)).ClearContents
'Sheets("표").Range("K2", Sheets("표").Cells(Rows.Count, "L").End(xlUp)).ClearContents

rngS = Sheets("전체").Range("A2", Sheets("전체").Cells(Rows.Count, "C").End(xlUp))
rngFA = Sheets("표").Range("A2", Sheets("표").Cells(Rows.Count, "A").End(xlUp))
rngFD = Sheets("표").Range("D2", Sheets("표").Cells(Rows.Count, "D").End(xlUp))
rngFG = Sheets("표").Range("G2", Sheets("표").Cells(Rows.Count, "G").End(xlUp))
rngFJ = Sheets("표").Range("J2", Sheets("표").Cells(Rows.Count, "J").End(xlUp))

'Debug.Print UBound(rngS, 1) '행 - 1차원
'Debug.Print UBound(rngS, 2) '열 - 2차원
ReDim arrA(1 To UBound(rngFA, 1), 1 To 2)
For i = 1 To UBound(rngFA, 1)
    For j = 1 To UBound(rngS, 1)
        If rngFA(i, 1) = rngS(j, 1) Then
'            sheets("표").cells(i,"B") = rngs(j,2)
            arrA(i, 1) = rngS(j, 2)
            arrA(i, 2) = rngS(j, 3)
        End If
    Next
Next


ReDim arrD(1 To UBound(rngFD, 1), 1 To 2)
For i = 1 To UBound(rngFD, 1)
    For j = 1 To UBound(rngS, 1)
        If rngFD(i, 1) = rngS(j, 1) Then
'            sheets("표").cells(i,"B") = rngs(j,2)
            arrD(i, 1) = rngS(j, 2)
            arrD(i, 2) = rngS(j, 3)
        End If
    Next
Next

ReDim arrG(1 To UBound(rngFG, 1), 1 To 2)
For i = 1 To UBound(rngFG, 1)
    For j = 1 To UBound(rngS, 1)
        If rngFG(i, 1) = rngS(j, 1) Then
'            sheets("표").cells(i,"B") = rngs(j,2)
            arrG(i, 1) = rngS(j, 2)
            arrG(i, 2) = rngS(j, 3)
        End If
    Next
Next

ReDim arrJ(1 To UBound(rngFJ, 1), 1 To 2)
For i = 1 To UBound(rngFJ, 1)
    For j = 1 To UBound(rngS, 1)
        If rngFJ(i, 1) = rngS(j, 1) Then
'            sheets("표").cells(i,"B") = rngs(j,2)
            arrJ(i, 1) = rngS(j, 2)
            arrJ(i, 2) = rngS(j, 3)
        End If
    Next
Next
Sheets("표").Range("B2").Resize(UBound(arrA, 1), 2) = arrA
Sheets("표").Range("E2").Resize(UBound(arrD, 1), 2) = arrD
Sheets("표").Range("H2").Resize(UBound(arrG, 1), 2) = arrG
Sheets("표").Range("K2").Resize(UBound(arrJ, 1), 2) = arrJ
Columns("A").ColumnWidth = 12
Columns("D").ColumnWidth = 12
Columns("G").ColumnWidth = 12
Columns("J").ColumnWidth = 12
Columns("B").ColumnWidth = 7
Columns("E").ColumnWidth = 7
Columns("H").ColumnWidth = 7
Columns("K").ColumnWidth = 7
Columns("C").ColumnWidth = 13.75
Columns("F").ColumnWidth = 13.75
Columns("I").ColumnWidth = 13.75
Columns("L").ColumnWidth = 13.75
End Sub