PDA

View Full Version : Array Item used correctly



mikke3141
12-25-2015, 07:06 AM
Hello,

I'm trying to populate the yellow cells with the same values as shown in the green cells using the dictionary object. What am I doing wrong?

I appreciate your help.

SamT
12-25-2015, 09:23 AM
http://www.snb-vba.eu/VBA_Dictionary_en.html
http://www.snb-vba.eu/VBA_Arrays_en.html

mikke3141
12-25-2015, 10:28 AM
Thanks. I studied the content of the links to be able to get the content of the first array to the second. I tried with


b = ActiveSheet.Range("J2:L9").Value
For i = 1 To UBound(b, 1)
If .Exists(b(i, 1)) Then
For ii = 1 To number_of_columns
b(i, ii) = .Item(b(i, 1))(ii)
Next ii
End If
Next i
End With

but the b(i, ii) = .Item(b(i, 1))(ii) did not bring the content of the first array, but gives a type mismath error.

mikke3141
12-25-2015, 11:21 AM
b(i, ii) = a(b(i, 1), (ii)) did not bring me any closer

SamT
12-25-2015, 12:52 PM
b(i, ii) = a(b(i, 1), (ii))
b(dimension, index) = a(dimension(index, index), (index))

mikke3141
12-25-2015, 01:42 PM
I give up :dunno

SamT
12-25-2015, 02:08 PM
This is meaningless

a(b(i, 1), (ii))

Show the entire code. What you showed in posts #3 and #4 is not enough.

snb
12-25-2015, 03:12 PM
You will have to put the values into the dictionary first

b = ActiveSheet.Range("J2:L9").Value
with createobject("scripting.dictionary")
For i = 1 To UBound(b, 1)
.item(b(i,1))=application.index(b,i)
Next
msgbox join(.item()(3))
End With

mikke3141
12-25-2015, 03:12 PM
The code is


Sub Start_It()

number_of_columns = 2
a = ActiveSheet.Range("A2:C30").Value
With CreateObject("Scripting.Dictionary")
For i = 1 To UBound(a, 1)
.Item(a(i, 1)) = a(i, 1)
For ii = 1 To number_of_columns
.Item(a(i, 1 + ii)) = a(i, 1 + ii)
Next ii
Next

b = ActiveSheet.Range("J2:L9").Value
For i = 1 To UBound(b, 1)
If .Exists(b(i, 1)) Then
For ii = 1 To number_of_columns
b(i, ii) = a(b(i, 1), (ii))
Next ii
End If
Next i
End With

ActiveSheet.Range("K2:L9").Value = b

End Sub