CODEWITHSUNDEEP
excelvbaobject
Abdulla Osama
Abdulla Osama

Reputation: 179

Dynamically Set Object name in for loop

I have a for loop with some conditions, I want to Create new Objects inside the for loop dynamically when met the conditions, i have commented the error in the code, Is there a way to accomplish this ? I am new to vba so xcuse my ignorant. Would appreciate any help.

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

Upvotes: 0

Views: 399

Answers (1)

Toddleson
Toddleson

Reputation: 4467

It seems like you are wanting a Collection of family_member dictionaries. You can accomplish this by creating either a Collection or a Dictionary outside the loop and then adding each family member to that collection during the loop.

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

At the end of the loop, you now have a Dictionary named Family that has one item for each familyMember. The items are accessible by their name like Family("George") and the item's items are accessible like Family("George")("relationship"). You can also loop through the collection directly like For Each Member In Family.Items

Upvotes: 1

Related Questions