0

The formula in question pulls fund holding data from Morningstar Direct. If I use the Morningstar UI, the code it generates on its own to my selected excel cell is as follows (for 1 fund at a time):

=@MSHOLDING("US52469L6609";"ISIN";"CORR=C, ASCENDING=TRUE, HT=ALL, 
WEIGHT=TRUE, FREQ=A, NAME=TRUE, SHOWHT=TRUE, SHOWCOUNTRY=FALSE")

This formula can then be typed or copied to a cell without the use of Morningstar UI, and it pulls the data to the sheet. However, since I want to do this for multiple (list) of funds, I've created this VBA macro for it. My code is as follows:

Public Sub Get_Fund_Holdings()

Dim item

For Each item In Array("US52469L6609", "US64128K8760")

    Sheets.Add
    ActiveSheet.Name = item
    
    ActiveSheet.Range("A1").Value = "Ticker"
    
    ActiveSheet.Range("B1").Formula = "=@MSHOLDING(""" & item & 
    """;""ISIN"";""CORR=C, ASCENDING=TRUE, HT=ALL, WEIGHT=TRUE, FREQ=A, 
    NAME=TRUE, SHOWHT=TRUE, SHOWCOUNTRY=FALSE"")"

    ActiveSheet.Range("A2").Value = item
    ActiveSheet.Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:A" & _
    Range("B" & Rows.Count).End(xlUp).Row) 
  
Next item
    
End Sub

I get the error message "Expected: end of statement" at the "ISIN" part of the code. Any idea why this is happening?

(I have the double "" on the item because it is a variable and this is how I think it is supposed to be done. The others are not variables in the formula that I can change.)

Edit: The variable problem has been solved thanks to Brian. Now, I get the error "Run-time error 1004:" on the =@MSHOLDING formula.

TexasTester
  • 9
  • 2

1 Answers1

0

You're not using the double quote correctly. See https://docs.microsoft.com/en-us/office/vba/access/concepts/criteria-expressions/include-quotation-marks-in-string-expressions

mbmast
  • 760
  • 7
  • 20