Array - Subscript out of range..

[Mallesh23]

Hi Team,

Unable to store data into Array getting subscript out of range.
Adding data in dictionary, storing output in array, and printing array in Range.

My data is in 150000 rows. so trying array and Dictionary Combination.

Expected output is in H and I Columns.
'Below attempted Code.

Sub Dict_array_Combination()

    Dim dict As New Scripting.dictionary
    Dim arr As Variant
    Dim i As Long
    Dim ary As Variant
   
    arr = Range("A2:D13").Value
   
       Dim ary_english As Variant
        Dim ary_Maths As Variant
   
        ary_english = Range("G2:G10").Value
        ary_Maths = Range("G2:G10").Value
   
   
    'Store in Dictionary
    With dict
        For i = LBound(arr, 1) To UBound(arr, 1)
            If Not .Exists(arr(i, 1)) Then
                .Add (arr(i, 1)), Array(arr(i, 2), arr(i, 4))
            End If
        Next i
   
    'Store Output in Array     'Can we store two columns into array.
        For i = LBound(ary_english, 1) To UBound(ary_english, 1)
              ary_english(i, 1) = .Item(ary_english(i, 1))  'Store English Marks.
              ary_Maths(i, 1) = .Item(ary_Maths(i, 4))  ' 'Store Maths Marks.
        Next i
    
    End With
    
    'Print Array Output in Range
    Range("H2").Resize(UBound(ary_english, 1)).Value = ary_english 'Print english Marks
    Range("I2").Resize(UBound(ary_Maths, 1)).Value = ary_Maths ''Print Maths Marks
    

End Sub

Book1
	A	B	C	D	E	F	G	H	I
1	Student	English	Hindi	Math			Student	English	Math
2	Salman	57	72	58			Salman	57	58
3	Shahrukh	91	52	50			Shahrukh	91	50
4	Hrithik	87	86	76			Hrithik	87	76
5	Priyanka	80	74	75			Priyanka	80	75
6	Katrina	79	84	92			Katrina	79	92
7	Alia	74	53	76			dharmendra
8	Madhuri	61	57	80			Madhuri	61	80
9	Amir	77	80	70			Amir	77	70
10	Amitabh	81	71	79			Govinda
11	Johny Lever	71	80	72			Amitabh	81	79
12	Juhi	71	80	72
13	Karishma	65	85	75
Sheet1

Cell Formulas
Range	Formula
H2:H11	H2	=IFERROR(VLOOKUP(G2,$A$1:$D$13,2,0),"")
I2:I11	I2	=IFERROR(VLOOKUP(G2,$A$1:$D$13,4,0),"")

 

[Mallesh23]

Hi Fluff,

frankly speaking, I don't have an idea of it, which to select from these =><vba/> , <rich/>,</>,

Thanks
mg

 

[Fluff]

Thanks for the tags

I'd stick to your original version, but create a new array

        Dim ary_student As Variant
        ary_student = Range("G2:G10").Value
        ReDim ary_english(1 To UBound(ary_student), 1 To 1)
        ReDim ary_Maths(1 To UBound(ary_student), 1 To 1)

and then populate the maths & English arrays like

        For i = LBound(ary_student, 1) To UBound(ary_student, 1)
            If .exists(ary_student(i, 1)) Then
               ary_english(i, 1) = .Item(ary_student(i, 1))(0)  'Store English Marks.
               ary_Maths(i, 1) = .Item(ary_student(i, 1))(1)  ' 'Store Maths Marks.
            End If
        Next i

 

[Fluff]

I would recommend using the <vba/> tags unless you want to highlight a piece of code like

        Dim ary_student As Variant
        ary_student = Range("G2:G10").Value
        ReDim ary_english(1 To UBound(ary_student), 1 To 1)
        ReDim ary_Maths(1 To UBound(ary_student), 1 To 1)

In which case use the <rich/> tags

 

[Mallesh23]

Hi Fluff,

Wow !!! Superrrrb piece of Code ?, Many many thanks for your help. ?

will remember tagging next time .

Thanks
mg

 

[Fluff]

You're welcome & thanks for the feedback.