テスト22

'表から指定の値を複数取得してコンボボックスに格納
------------------------------------------------

Sub collec()

UserForm2.Show vbModeless

End Sub

------------------------------------------------

Private Sub UserForm_Initialize()

Dim col As Collection
Set col = New Collection
Dim MaxRow As Long
Dim i As Long

Mrow = ThisWorkbook.Sheets("Sheet7").Cells(Rows.Count, 2).End(xlUp).Row

For i = 3 To Mrow

    With ThisWorkbook.Sheets("Sheet7")

        col.Add Item:=.Cells(i, 2).value & "," & .Cells(i, 3).value, Key:=CStr(i - 2)

    End With

Next

ComboBox2.ColumnCount = 2
ComboBox2.ColumnWidths = "40;50"

Dim tmp() As String

For i = 1 To col.Count

    With ComboBox2

        tmp = Split(col.Item(i), ",")
        .AddItem tmp(0)
        .List(i - 1, 0) = tmp(0) & vbTab & tmp(1)
        .List(i - 1, 1) = tmp(1)

    End With

    Erase tmp

Next

End Sub

'------------------------------------------

Type Hyou

    ID As Variant
    Kenmei As Variant

End Type

'構造体による動的疑似二次元配列
Sub Kouzoutai_Test()

Dim Hyou2() As Hyou

ReDim Preserve Hyou2(0)

Dim MaxRow As Long
Dim i As Long

Mrow = ThisWorkbook.Sheets("Sheet7").Cells(Rows.Count, 2).End(xlUp).Row

With ThisWorkbook.Sheets("Sheet7")

    For i = 3 To Mrow

        ReDim Preserve Hyou2(i - 3)

        Hyou2(i - 3).ID = .Cells(i, 2).value
        Hyou2(i - 3).Kenmei = .Cells(i, 3).value

    Next

End With



For i = LBound(Hyou2) To UBound(Hyou2)

    Debug.Print Hyou2(i).ID

    Debug.Print Hyou2(i).Kenmei

Next


End Sub