Can’t retrieve a value from my dictionary using its key using vba

By Obie_One

enter image description here

I am completely stumped on this problem here. I created a macro that will read values from the current spreadsheet, place those values into a dictionary using row numbers as keys, create a new spreadsheet, grab the values from those dictionaries, and add them to the new spreadsheet. There are three dictionaries that are filled. I have no problem getting the values from two of the dictionaries and I even have no problems getting the first couple of values from the problematic dictionaries. But when I try to retrieve the last two values in the last For Next loop, the values are read as “” instead of an actual value. The image below is a message that I built from looping the problematic dictionary.

I had the debug loop that produced this message within the last For Next loop. As you can see each key has a value, but when I use dataN.Exist(key) for just the last two values I get “” as the value. I don’t understand. The exact same code works to pull the first couple of values but not the last couple. I have even moved those values to different rows but still got the same “”. Here is the entire code here below:

Sub Transfer2NewWorkbook()
Dim currentsheet As String
Dim newsheet As String
Dim analysisDate As String
Dim initial As String
Dim aInitial() As String
Dim analystInit As String
Dim aBatch() As String
Dim batch As String
Dim batchNo As String
Dim key As Variant
Dim ikey As Variant
Dim SrowN As String
Dim rowN As Integer
Dim rowD As String
Dim wb As Object
Dim dataRangeN As Range, dataRangeB As Range, dataRangeI As Range
Dim dataN As Object
Set dataN = CreateObject("Scripting.Dictionary")
Dim dataB As Object
Set dataB = CreateObject("Scripting.Dictionary")
Dim dataI As Object
Set dataI = CreateObject("Scripting.Dictionary")
Dim teststring As String
' Grab and Create filenames
currentsheet = ActiveWorkbook.Name
newsheet = currentsheet & "-" & "uploadable"
' Grab data from original spreadsheet
analysisDate = ActiveWorkbook.Sheets(1).Cells(1, 9).Value
initial = ActiveWorkbook.Sheets(1).Cells(1, 2).Value
aInitial = Split(initial, "/")
analystInit = aInitial(1)
batch = ActiveWorkbook.Sheets(1).Cells(1, 4).Value
aBatch = Split(batch, ":")
batchNo = aBatch(1)
Set dataRangeN = Range("A:A")
Set dataRangeB = Range("B:B")
Set dataRangeI = Range("I:I")
For i = 4 To dataRangeB.Rows.Count
    If Not IsEmpty(dataRangeB(i, 1)) Then
        If StrComp(ActiveWorkbook.Sheets(1).Cells(i, 2).Value, "End") = 0 Then
            Exit For
        ElseIf StrComp(ActiveWorkbook.Sheets(1).Cells(i, 2).Value, "Blank") = 0 Then
             If StrComp(ActiveWorkbook.Sheets(1).Cells(i, 1).Value, "Unseeded") = 0 Or StrComp(ActiveWorkbook.Sheets(1).Cells(i, 1).Value, "Seeded") = 0 Then
                If Not IsEmpty(dataRangeI(i, 1)) Then
                    dataN.Add i, ActiveWorkbook.Sheets(1).Cells(i, 1).Value
                    dataB.Add i, ActiveWorkbook.Sheets(1).Cells(i, 2).Value
                    dataI.Add i, ActiveWorkbook.Sheets(1).Cells(i, 9).Value
                End If
             End If
        ElseIf StrComp(ActiveWorkbook.Sheets(1).Cells(i, 2).Value, "Check") = 0 Then
            If StrComp(ActiveWorkbook.Sheets(1).Cells(i, 1).Value, "Std") = 0 Then
                If Not IsEmpty(dataRangeI(i, 1)) Then
                    dataN.Add i, ActiveWorkbook.Sheets(1).Cells(i, 1).Value
                    dataB.Add i, ActiveWorkbook.Sheets(1).Cells(i, 2).Value
                    dataI.Add i, ActiveWorkbook.Sheets(1).Cells(i, 9).Value
                End If
            End If
        ElseIf StrComp(ActiveWorkbook.Sheets(1).Cells(i, 2).Value, "DUP") = 0 Then
            rowD = dataB.Keys()(dataB.Count - 1)
            If StrComp(ActiveWorkbook.Sheets(1).Cells(i - 1, 1).Value, "CBOD") = 0 Then
                dataN.Add rowD, "DUP-CBOD"
            ElseIf StrComp(ActiveWorkbook.Sheets(1).Cells(i - 1, 1).Value, "BOD") = 0 Then
                dataN.Add rowD, "DUP-BOD"
            End If
        Else
            dataB.Add i, ActiveWorkbook.Sheets(1).Cells(i, 2).Value
            dataI.Add i, ActiveWorkbook.Sheets(1).Cells(i, 9).Value
        End If
    Else
        If StrComp(ActiveWorkbook.Sheets(1).Cells(i, 1).Value, "DUP") = 0 Then
            rowD = dataB.Keys()(dataB.Count - 1)
            If StrComp(ActiveWorkbook.Sheets(1).Cells(i - 1, 1).Value, "CBOD") = 0 Then
                dataN.Add rowD, "DUP-CBOD"
            ElseIf StrComp(ActiveWorkbook.Sheets(1).Cells(i - 1, 1).Value, "BOD") = 0 Then
                dataN.Add rowD, "DUP-BOD"
            End If
        End If
    End If
Next i
' Open new spreadsheet
Set wb = Workbooks.Add("C:Usersdalythedocumentsuploadtemp.xlsx")
ActiveWorkbook.Sheets(1).Cells(2, 2).Value = analysisDate
ActiveWorkbook.Sheets(1).Cells(2, 4).Value = analystInit
ActiveWorkbook.Sheets(1).Cells(3, 5).Value = batchNo
rowN = 4
For Each key In dataB.Keys
    If dataI.Exists(key) Then
        SrowN = CStr(rowN)
        If dataN.Exists(key) Then
            ActiveWorkbook.Sheets(1).Cells(SrowN, 1).Value = dataN(key)
        End If
        ActiveWorkbook.Sheets(1).Cells(SrowN, 2).Value = dataB(key)
        ActiveWorkbook.Sheets(1).Cells(SrowN, 3).Value = dataI(key)
        rowN = CInt(SrowN)
        rowN = rowN + 1
    End If
Next
ActiveWorkbook.SaveAs (newsheet & ".xlsx")
End Sub

Source: Stack Overflow

    

Share it with your friends!

    Fatal error: Uncaught Exception: 12: REST API is deprecated for versions v2.1 and higher (12) thrown in /home/content/19/9652219/html/wp-content/plugins/seo-facebook-comments/facebook/base_facebook.php on line 1273