Private Function skipping For Loop

Question

I am trying to now learn how Dictionaries work within VBA, so I created a simple Class Module, A Function, and then two subs, but for reasons beyond me the For loop is completely skipped within the function. Below is the Code for all of the items mentioned above. I do have the Microsoft Scripting Runtime checked in Tools > References. Im not really familiar with how Late and Early Binding are utilized, so I'm wondering if that's one of the issues.

Currently the Set rg = LoanData.Range("AH2") is in a table, I have tried the data in that range as both a table and also as just a range, but the For Loop in the function is skipped if the data is in a Table or not.

Class Module called clsCounty

Public CountyID As Long
Public County As String

Function called ReadCounty

Private Function ReadCounty() As Dictionary
    Dim dict As New Dictionary

    Dim rg As Range
    Set rg = LoanData.Range("AH2")

    Dim oCounty As clsCounty, i As Long


    For i = 2 To rg.Rows.Count

        Set oCounty = New clsCounty

        oCounty.CountyID = rg.Cells(i, 1).Value
        oCounty.County = rg.Cells(i, 2).Value

        dict.Add oCounty.CountyID, oCounty
    Next i

    Set ReadCounty = dict


End Function

The two subs to write to the immediate window

Private Sub WriteToImmediate(dict As Dictionary)
    Dim key As Variant, oCounty As clsCounty

    For Each key In dict.Keys
        Set oCounty = dict(key)
        With oCounty
            Debug.Print .CountyID, .County
        End With
    Next key

End Sub

Sub Main()
    Dim dict As Dictionary

    Set dict = ReadCounty

    WriteToImmediate dict

End Sub

Answer 1

You've declared your range as Set rg = LoanData.Range("AH2") and then use For i = 2 To rg.Rows.Count in your loop. the rg.Rows.Count will be 1 as there is only 1 cell in your range. This is before the starting value for your For loop (2) so it won't do anything.

i.e. For i = 2 to 1

Declare your rg variable with the full range. I'm going to guess something like

With LoanData
    Set rg = .Range(.Cells(1,"AH"), .Cells(.Cells(.Rows.Count, "AH").End(xlUp).Row, "AH"))
End With

Answer 2

The problem is indeed in the usage of Set rg = LoanData.Range("AH2"), as mentioned in the other answer.

However, to be a bit more elegant, you may consider using LastRow, function, which takes as arguments columnToCheck and wsName:

Public Function LastRow(wsName As String, Optional columnToCheck As Long = 1) As Long

    Dim ws As Worksheet
    Set ws = Worksheets(wsName)
    LastRow = ws.Cells(ws.Rows.Count, columnToCheck).End(xlUp).Row

End Function

In the code, it would look like this:

Private Function ReadCounty() As Dictionary

    Dim dict As New Dictionary
    Dim oCounty As clsCounty, i As Long

    'For i = 2 To LastRow("LoanData", 34)
    For i = 2 To LastRow(LoanData.Name, Range("AH1").Column) 
        Set oCounty = New clsCounty
        oCounty.CountyID = LoanData.Cells(i, "AH").Value
        oCounty.County = LoanData.Cells(i, "AI").Value
        dict.Add oCounty.CountyID, oCounty
    Next i

    Set ReadCounty = dict

End Function