Loop through range in one sheet and have custom formula for each cell. Put data into a new sheet [excel][vba][bloomberg]

Question

I'm not sure how descriptive my title is but below I will try to explain what I am trying to do.

• I have a list of company Bloomberg tickers => maybe more than 100
• This list of tickers is saved in say "Sheet1" "A:A"
• For each ticker I have a Bloomberg (BDS) formula that returns a certain number of shareholders
• The number of shareholders needs to be dynamic
• The ticker of each company needs to be copied alongside the info gathered from BBG (as this is not provided by their formula)
• This whole data should be in a new sheet let's say "Sheet2"

Below is the code I am using. It actually does what I need, however I am not able to make the output of my FOR loop to be in a new sheet. I also believe that the writing is not the most efficient so any help there would be great.

My current excel spreadsheet: How it looks now

Sub Macro1()
'
' Macro1 Macro
'


Dim ticker As Range
Dim cell As Range
Dim start_row As Integer
Dim row As Integer
Dim top_investors As Integer

top_investors = 5
start_row = 2

Range("K2:U999999").ClearContents
Range("L" & start_row).Select
For Each ticker In Range("A2:A" & Cells(Rows.Count, 2).End(xlUp).row)
    If Not IsEmpty(ticker) Then
        ActiveCell.Formula = "=BDS(" & Chr(34) & ticker.Value & Chr(34) & ",""TOP_20_HOLDERS_PUBLIC_FILINGS"",""Endrow""," & Chr(34) & top_investors & Chr(34) & ",""Endcol"",""9"")"
    
        For Each cell In Range("K" & start_row & ":" & "K" & start_row + top_investors)
               row = start_row
               Range("K" & row).Select
               cell.Value = ticker.Value
               row = row + 1
               
        Next cell
        start_row = start_row + top_investors
    Range("L" & start_row).Select
    End If
Next ticker

End Sub

Answer 1

Well, I guess you need to do something like:

Sub Macro1()
'
' Macro1 Macro
'


Dim ticker As Range
Dim cell As Range
Dim start_row As Integer
Dim row As Integer
Dim top_investors As Integer
Dim mySheet as Worksheet

top_investors = 5
start_row = 2
Sheets.Add.Name = "myNewSheet"
mySheet = ThisWorkbook.Sheets("myNewSheet")

Range("K2:U999999").ClearContents
Range("L" & start_row).Select
For Each ticker In Range("A2:A" & Cells(Rows.Count, 2).End(xlUp).row)
    If Not IsEmpty(ticker) Then
        mySheet.Range("define your Range").Formula = "=BDS(" & Chr(34) & ticker.Value & Chr(34) & ",""TOP_20_HOLDERS_PUBLIC_FILINGS"",""Endrow""," & Chr(34) & top_investors & Chr(34) & ",""Endcol"",""9"")"
    
        For Each cell In (mySheet?.)Range("K" & start_row & ":" & "K" & start_row + top_investors)
               row = start_row
               mySheet.Range("K" & row).Select
               cell.Value = ticker.Value
               row = row + 1
               
        Next cell
        start_row = start_row + top_investors
    Range("L" & start_row).Select
    End If
Next ticker

End Sub

That won't work yet, but I guess you got the idea of working with "mySheet" and know better than me were to add it in your code. Maybe you need to google a little bit about the correct syntax.

Answer 2

If you have a recent Excel version (that has the LET spreadsheet function) then VBA is not strictly necessary for what you want to achieve.

For example, if you tickers are in the range A4:A7 on 'Sheet 1', you can collect all the data in 'Sheet 2':

The formula in Sheet2!A4: =Sheet1!A4

The formula in Sheet2!B4: =LET(data,BDS(A4,$B$2,"Array=TRUE"),s,SEQUENCE(1,$B$1),INDEX(data,s,1))

Then fill the formulae down as needed. The dynamic number of investors is in cell Sheet2!B1, but this can be hard-coded.

The main 'trick' is to use the "Array=TRUE" option in the BDS() call. This returns the data in a dynamic array, which can be indexed into.