VBA: looping in an array and select the corresponding value

[Nelson78]

Hello everybody.

I've a situation as follows.

https://imgur.com/HLpoOUH


Then, in sheet "Data" I have two values:
cell A2 = 7210210
cell A3 = 7210214

Now, I've to loop in this array and write in B2 = 2958 (corresponding to 7210210) and in B3 = 1020 (corresponding to 7210214).

How can I perform this task?


Thank's in advance.

 

[Fluff]

This looks like you would be better off using a Dictionary, rather than an array.
https://excelmacromastery.com/vba-dictionary/

 

[Nelson78]

This looks like you would be better off using a Dictionary, rather than an array.
https://excelmacromastery.com/vba-dictionary/

I know and I've been trying, but without much success.

https://imgur.com/RZsAlPu

 

[Fluff]

If you would like help, then you need to do a bit more than post images of the locals window, which tell us virtually nothing.

 

[Nelson78]

Ok.

Sheet "Sheet1" column K: I have a huge amount of rows with repeated and not repeated values, including 7210210 and 7210214.
Sheet "Sheet1" column D: same situation with repeated and not repeated values.

Sheet "data" cell A2 = 7210210
Sheet "data" cell A3 = 7210214

I have to fill:
Sheet "data" cell B2 = how many unique values in "Sheet1" column D for 7210210 in column K?
Sheet "data" cell B3 = how many unique values in "Sheet1" column D for 7210214 in column K?

 

[Norie]

Are you trying to get a list of the unique values and the no of times they occur in columns D an K on Sheet1?

Or are you trying to find get a list of all the values in column D where the corresponding value in column K is 7210210 and similarly for 7210214?

Can you post some sample data?

 

[Fluff]

What code do you have?
And in what way doesn't it work?

Finally roughly how many rows do you have in Sheet1?

 

[Nelson78]

Are you trying to get a list of the unique values and the no of times they occur in columns D an K on Sheet1?

Or are you trying to find get a list of all the values in column D where the corresponding value in column K is 7210210 and similarly for 7210214?

Can you post some sample data?

By sight, la situation is like this.

https://imgur.com/HgV7pxM

Rows populated: about 10k

 

[Fluff]

Any chance you can post your code?

 

[Nelson78]

Any chance you can post your code?

sub count

    Dim vData As Variant
    Dim vOutput() As Variant
    Dim vKey
    Dim dicCount As Object
    Dim dicTemp As Object
    Dim i As Long, j As Long


Dim lr As Long
    lr = Worksheets("Sheet1").Cells(Rows.Count, "H").End(xlUp).row

    With Sheets("Sheet1").
vData = .Range("D2:K" & lr)
        
For i = LBound(vData, 1) + 1 To UBound(vData, 1)
        
Set dicCount = CreateObject("scripting.dictionary")
            If dicCount.Exists(vData(i, 8)) Then
                dicCount(vData(i, 8))(vData(i, 1)) = dicCount(vData(i, 8))(vData(i, 1)) + 1
            Else
                Set dicTemp = CreateObject("scripting.dictionary")
                dicTemp(vData(i, 1)) = 1
                Set dicCount(vData(i, 8)) = dicTemp
            End If
        Next i

        ReDim vOutput(1 To dicCount.Count, 1 To 2)
        i = 1
        For Each vKey In dicCount.Keys
            vOutput(i, 1) = vKey
            vOutput(i, 2) = dicCount(vKey).Count
            i = i + 1
        Next vKey

end sub