CODEWITHSUNDEEP
excelvbaloopsrange
nikolayDudrenov
nikolayDudrenov

Reputation: 169

VBA - create a Dictionary from multiple worksheets and ranges

I'm creating a dictionary from multiple worksheets and ranges. My code is working, but is very unpleasant to look at. There should be something fundamental missing in my knowledge, which is not surprise, as this is my first project in VBA. How to achieve this in a single loop? Any help is highly appreciated.

    ' Get the range of all the adjacent data using CurrentRegion
Dim rg1 As Range, rg2 As Range, rg3 As Range, rg4 As Range, rg5 As Range, rg6 As Range, rg7 As Range
Set rg1 = sheet1.Range("A1").CurrentRegion
Set rg2 = sheet2.Range("A1").CurrentRegion
Set rg3 = sheet3.Range("A1").CurrentRegion
Set rg4 = sheet4.Range("A1").CurrentRegion
Set rg5 = sheet5.Range("A1").CurrentRegion
Set rg6 = sheet6.Range("A1").CurrentRegion
Set rg7 = sheet7.Range("A1").CurrentRegion


Dim oID As clsItem, i As Long, j As Long, Id As Long
'read through the data

    For i = 3 To rg1.rows.count

        Id = rg1.Cells(i, 1).value

        ' Create a new clsDetector object
        Set oID = New clsItem

        ' Add the new clsDetector object to the dictionary
        dictName.add Id, oID

        ' Set the values
        oID.ItemName = rg1.Cells(i, 70).value

    Next i

'
'
'Same loops for rg2, rg3, rg4, rh5, rg6 and rg7
'
'

Upvotes: 1

Views: 502

Answers (1)

Elio Fernandes
Elio Fernandes

Reputation: 1420

Since the sheets goes from 1 to 7 you can loop through them like this.

Sub LoadRangesIntoDict()
    Dim i As Integer
    Dim s As Integer
    Dim ws As Worksheet
    Dim rng As Range
    
    Dim oID As clsItem, i As Long, j As Long, Id As Long
    
    ' Loop through each sheet
    For s = 1 To 7
        Set ws = Sheets("Sheet" & s)
        Set rng = ws.Range("A1").CurrentRegion
        
        'read through the data
        For i = 3 To rng.Rows.Count

            Id = rng.Cells(i, 1).Value

            ' Create a new clsDetector object
            Set oID = New clsItem

            ' Add the new clsDetector object to the dictionary
            dictName.Add Id, oID

            ' Set the values
            oID.ItemName = rng.Cells(i, 70).Value
            
        Next i
    Next s
End Sub

Upvotes: 2

Related Questions