In Excel Has Data in a Cell that is linked to Google Finance have extra information that interferes with code for range.value?

I have a cell that is linked to Google Finance Stock Ticker. It replaces the Ticker with the stock name and a link (bank icon) included that brings up more data. I wish to have the stock name be added to a range cell using .value or value2. but I think that the included link to additional data interferes with the function. It returns an error code 2015. I have been able to have it add the stock name using .formula but because my range also includes another column that requires .value to return value of it's cell. I can't figure out how to get the value of both to work.

Here is the immediate window result for .Value Error 2015 2440 Error 2015 1945.2

Here is the immediate window result for .Formula iSharesNASDAQ100 Idx ETF (C-H) (XTSE:XQQ) =F6J6 iShares S&P/TSX 60 Index ETF (XTSE:XIU) =F7J7

Here is the code:

arr = .Range(.Cells(6, "C"), .Cells(.Rows.Count, "D").End(xlUp)).Formula
    Debug.Print arr(1, 1) '     which is C (column, row format)
    Debug.Print arr(1, 2) ' which is column d
    Debug.Print arr(2, 1) '     which is C (column, row format)
    Debug.Print arr(2, 2) ' which is column d

I am stumped. Any help?

  For i = LBound(arr, 1) To UBound(arr, 1)  ' Loop through and assign to dict.
        k = arr(i, 1)               'the key
        amt = arr(i, 2)             'the amount
        
        Debug.Print arr(1, 1)
        dict(k) = dict(k) + amt     'sum amount for this key
    Next i

    'return new values to worksheet
    .Cells(1, "W").Resize(1, 2) = Array("Company", "Value")
    .Cells(2, "W").Resize(dict.Count, 1) = Application.Transpose(dict.Keys)
    .Cells(2, "X").Resize(dict.Count, 1) = Application.Transpose(dict.items)
    With .Range(.Cells(1, "W"), .Cells(.Rows.Count, "X").End(xlUp))
          .Sort key1:=.Columns(2), order1:=xlDescending, _
                key2:=.Columns(1), order2:=xlAscending, _
                Header:=xlYes

    End With

Solution 1:

To demonstrate using two arrays of the same size:

Dim lastRow As Long
lastRow = .Cells(.Rows.Count, "D").End(xlUp).Row

Dim formulas() As Variant
formulas = .Range("C6:C" & lastRow).Formula

Dim vals() As Variant
vals = .Range("D6:D" & lastRow).Value

Dim i As Long
For i = LBound(formulas, 1) To UBound(formulas, 1)
    k = formulas(i, 1)
    amt = vals(i, 1)

    dict(k) = dict(k) + amt
Next