Help with Type mismatch in dictionary

[Dundee Lad]

Hello board, i am hoping someone can help me.

I have reused something i picked up a long time ago for a new purpose but i cant seem to get it to work.

Sub Calculate_Period_Headcount()
    
    Dim ws As Worksheet
    Dim i As Long
    Dim EndRow As Long
    Dim arr1 As Variant ' Column A UIN
    Dim arr2 As Variant ' Column AI Period
    Dim arr3 As Variant ' Column BZ FinanceTLRDictionary Value
    Dim arr4 As Variant ' Column BX This is then the FinanceTLR Value that
    Dim dic As Object

    Set ws = ThisWorkbook.Worksheets("Staging_Spain")
    Set dic = CreateObject("Scripting.Dictionary")
    
    With ws
        EndRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        arr1 = .Range("A2:A" & EndRow).Value
        arr2 = .Range("AI2:AI" & EndRow).Value
        arr3 = .Range("BZ2:BZ" & EndRow).Value

        For i = 1 To UBound(arr1)
            dic(arr1(i, 1) & "|" & arr2(i, 1)) = dic(arr1(i, 1) & "|" & arr2(i, 1)) + 1
        Next
        
        ReDim arr4(1 To UBound(arr1), 1 To 1)
        For i = 1 To UBound(arr1)
            arr4(i, 1) = arr3(i, 1) / dic(arr1(i, 1) & "|" & arr2(i, 1))
        Next
    
        .Range("BX2").Resize(UBound(arr5)).Value = arr4
    End With

End Sub

the values in column A are like a ref number = 1234567890, 2345678901 etc
the values in column AI are period values = 2018-19(P01), 2018-19(P02) etc
the values in column BZ are all 1.00

can anyone help me with this?

 

[RoryA]

Want to give us a clue where the error happens?

 

[Dundee Lad]

yes, apologies,

.Range("BX2").Resize(UBound(arr5)).Value = arr4

error says type mismatch.

 

[Fluff]

What is arr5?
You only use it at the end

 

[Dundee Lad]

arr5 is a typo, didnt see that, thank you!

I did amend this to arr4 and it runs ok now but the destination column BX is blank, no values

 

[Fluff]

It works for me on some test data.
Try stepping through the code & use the locals window to check what is being put into arr4

 

[Dundee Lad]

all arrays are empty, not sure why, will check and see what is causing it