Amend/understand VBA formula that includes Scripting Dictionaries

[ElBB23]

Hi all,

I have the below code in part of my excel vba that I need to amend but could do with some help understanding.

In cells T, W, and Z there is a sum if formula in row 2, this VBA replicates this formula down to the last row. I am trying to update the formula so that it does this for column T,W,Z,AC and AF

I've changed the 1-3 to 1-5 but it is debugging at the doc(ii) line.

Please could anyone help me up understand and update it.

Sub test()
    Dim a, k, i As Long, ii As Long, t As Long, w(1 To 3), x, dic(1 To 3) As Object
    With Range("k2", Range("k" & Rows.Count).End(xlUp))
        k = .Value
        a = .Columns(8).Resize(, 10).Value
    End With
    For i = 1 To 3
        Set dic(i) = CreateObject("Scripting.Dictionary")
        dic(i).CompareMode = 1
        ReDim x(1 To UBound(a, 1), 1 To 1) As Double: w(i) = x
    Next
    For i = 1 To UBound(a, 1)
        For ii = 1 To 3
            dic(ii)(a(i, (ii - 1) * 3 + ii + 1)) = i
        Next
    Next
    For i = 1 To UBound(a, 1)
        For ii = 1 To 3
            t = (ii - 1) * 3 + ii
            If dic(ii).exists(a(i, t)) Then
                x = w(ii)
                x(dic(ii)(a(i, t)), 1) = x(dic(ii)(a(i, t)), 1) + k(i, 1)
                w(ii) = x
            End If
        Next
    Next
    For i = 1 To 3
        Cells(2, (i + 4) * 4).Resize(UBound(a, 1)).Value = w(i)
    Next
End Sub

 

[ElBB23]

When I change any 1-3 to 1-5 it runs to the dic(ii)(a line. It then debugs saying the variable is not in the code.

 

[Fluff]

What is your amended code?

 

[ElBB23]

Hi, it's.

Sub test()

    Dim a, k, i As Long, ii As Long, t As Long, w(1 To 5), x, dic(1 To 5) As Object

    With Range("k2", Range("k" & Rows.Count).End(xlUp))

        k = .Value

        a = .Columns(8).Resize(, 10).Value

    End With

    For i = 1 To 5

        Set dic(i) = CreateObject("Scripting.Dictionary")

        dic(i).CompareMode = 1

        ReDim x(1 To UBound(a, 1), 1 To 1) As Double: w(i) = x

    Next

    For i = 1 To UBound(a, 1)

        For ii = 1 To 5

            dic(ii)(a(i, (ii - 1) * 3 + ii + 1)) = i

        Next

    Next

    For i = 1 To UBound(a, 1)

        For ii = 1 To 5

            t = (ii - 1) * 3 + ii

            If dic(ii).exists(a(i, t)) Then

                x = w(ii)

                x(dic(ii)(a(i, t)), 1) = x(dic(ii)(a(i, t)), 1) + k(i, 1)

                w(ii) = x

            End If

        Next

    Next

    For i = 1 To 5

        Cells(2, (i + 4) * 4).Resize(UBound(a, 1)).Value = w(i)

    Next

End Sub

I can normally work my way around code and understand it but this one has stumped me, it's such a brilliant code though I'd like to know how it's working.

 

[Fluff]

You need to change the resize on this line

a = .Columns(8).Resize(, 10).Value

it now needs to be 18

 

[ElBB23]

Absolutely fantastic thank you!

The code is running, just need to figure out why it's doing the wrong columns now! I'm messing with the code to try and fix it myself.

If possible would you be able to explain the lines of the code for me, I'd love to understand exactly what it's doing

 

[Fluff]

If possible would you be able to explain the lines of the code for me

Not really, because I have no idea what you are trying do.