在for回圈中動態設定物件名稱-有解無憂

我有for loop一些條件，我想Objects在 for 回圈中創建新dynamically的conditions，我已經在代碼中注釋了錯誤，有沒有辦法做到這一點？我是vba的新手，所以請原諒我的無知。將不勝感激任何幫助。

Set rng1 = ThisWorkbook.Worksheets("Calculator").Range("M16:M30")
x = 1
For Each rcell In rng1.Cells
    If Not IsEmpty(rcell) Then
        If rcell = "1" Then
            Set family_member_&x = CreateObject("Scripting.Dictionary")       // ERROR HERE
            family_member_&x.Add "family_group", "FG_1"
            family_member_&x.Add "name", ThisWorkbook.Worksheets("Calculator").Range("B"&x)
            family_member_&x.Add "date_of_birth", ThisWorkbook.Worksheets("Calculator").Range("C"&x)
        ElseIf rcell = "2" Then
            Set family_member_&x = CreateObject("Scripting.Dictionary")
            family_member_&x.Add "family_group", "FG_2"
            family_member_&x.Add "name", ThisWorkbook.Worksheets("Calculator").Range("B"&x)
            family_member_&x.Add "relationship", ThisWorkbook.Worksheets("Calculator").Range("E"&x)        
        End If
        x = x  1
    End If
        

Next rcell

uj5u.com熱心網友回復：

似乎您想要一個家庭成員字典的集合。您可以通過在回圈外創建一個集合或字典，然后在回圈期間將每個家庭成員添加到該集合來完成此操作。

Sub Example()
    Dim Family As Object, familyMember As Object
    Set Family = CreateObject("Scripting.Dictionary")
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Calculator")
    
    Dim rng1 As Range
    Set rng1 = ws.Range("M16:M30")
    
    Dim rCell As Range
    For Each rCell In rng1.Cells
        If rCell = "1" Then
            Set familyMember = CreateObject("Scripting.Dictionary")
            familyMember.Add "family_group", "FG_1"
            familyMember.Add "name", ws.Range("B" & rCell.Row)
            familyMember.Add "date_of_birth", ws.Range("C" & rCell.Row)
            Family.Add familyMember("name"), familyMember
        ElseIf rCell = "2" Then
            Set familyMember = CreateObject("Scripting.Dictionary")
            familyMember.Add "family_group", "FG_2"
            familyMember.Add "name", ws.Range("B" & rCell.Row)
            familyMember.Add "relationship", ws.Range("E" & rCell.Row)
            Family.Add familyMember("name"), familyMember
        End If
    Next rCell
    
End Sub

在回圈結束時，您現在有一個名為 Dictionary 的字典Family，其中每個familyMember. 這些專案可以通過它們的名稱訪問，Family("George")并且專案的專案可以通過訪問Family("George")("relationship")。您也可以像這樣直接遍歷集合For Each Member In Family.Items

轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/419975.html

標籤：

上一篇：基于另一個單元格突出顯示一個單元格中的特定文本

下一篇：獲取excel顯示值