0

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
geddeca
  • 119
  • 1
  • 1
  • 9
  • As mentioned in a comment to your previous question, I think you might need to be creative in creating `arr`. Possibly with a loop. – BigBen Jan 12 '22 at 20:11
  • Or use two arrays, one for column C using `.Formula` and one for D using `.Value`, depending on your spreadsheet setup. – BigBen Jan 12 '22 at 20:27
  • Yes, I did see that answer. I certainly can do 2 arrays but I am not sure that I have the skill to then put them into the Dict object and sort etc. that I want to do. I will put the code in the original post to show you. because I don't think I can put it here. I am shaky with the dictionary object and pulled it apart on an example that had 2 columns. Not sure how to use it properly which is why I avoided reworking it so far. – geddeca Jan 12 '22 at 22:53
  • Another thought was to make insert an extra column that has a formula that takes the company column (C) and returns the value or formula to the new column D6 less all of the linking code. If that makes sense. Something like D6 =.Range(.Cells(6, "C").Formula (This doesn't work but hopefully I can figure out the syntax that will.) I don't have enough knowledge of excel objects to use them properly. – geddeca Jan 12 '22 at 23:06
  • I tested just manually copying the whole column to an inserted column and right clicked. changed datatype to text. (changed the code addresses to match then ran the code with .Value2. It worked. and is easier than rewriting the vba. Do you know if there is a way that I can do that with code as I wrote above? I don't know if it is a syntax error I am making or is it just not possible to do in a cell from the formula bar. – geddeca Jan 13 '22 at 00:47

1 Answers1

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
BigBen
  • 46,229
  • 7
  • 24
  • 40
  • 1
    Ok, Thank you. I see that you separated the columns into 2 variables (I think). I will need to analyze better when I have some time later today. It looks like more simple code. than the other. It is a lot better than the dummy column I was going to use . Thank you Again. – geddeca Jan 13 '22 at 17:35
  • Correct, there's two arrays of the same size, one for each column. – BigBen Jan 13 '22 at 17:36