Excel VBA遍历工作表单元格并应用自定义Bloomberg公式，将结果输出至新工作表的实现问题

我明白你的需求：从Sheet1的A列批量读取股票代码，用BDS公式获取指定数量的股东数据，同时把股票代码和对应数据绑定输出到Sheet2，还要优化现有代码的效率和写入问题。下面是针对你的需求优化后的代码，以及关键改进点的说明：

优化后的完整代码 #

Sub GetBloombergShareholders()
    Dim wsSource As Worksheet
    Dim wsOutput As Worksheet
    Dim lastRowSource As Long
    Dim currentOutputRow As Long
    Dim topInvestors As Integer
    Dim tickerCell As Range
    
    ' 配置参数：可动态调整需要提取的股东数量
    topInvestors = 5
    
    ' 定义源工作表和输出工作表
    Set wsSource = ThisWorkbook.Sheets("Sheet1")
    Set wsOutput = ThisWorkbook.Sheets("Sheet2")
    
    ' 清空输出工作表旧数据（保留表头如果有的话，这里从第2行开始清空）
    wsOutput.Range("A2:Z" & wsOutput.Rows.Count).ClearContents
    
    ' 获取Sheet1中A列最后一行有数据的行号
    lastRowSource = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    ' 初始化输出起始行
    currentOutputRow = 2
    
    ' 遍历Sheet1中A列的股票代码（从第2行开始，假设第1行是表头）
    For Each tickerCell In wsSource.Range("A2:A" & lastRowSource)
        If Not IsEmpty(tickerCell.Value) Then
            ' 1. 在Sheet2的B列写入BDS公式，一次性获取指定数量的股东数据
            wsOutput.Cells(currentOutputRow, "B").Formula = _
                "=BDS(""" & tickerCell.Value & """,""TOP_20_HOLDERS_PUBLIC_FILINGS"",""Endrow"",""" & topInvestors & """,""Endcol"",""9"")"
            
            ' 2. 一次性填充对应行数的股票代码到Sheet2的A列，替代循环赋值提升效率
            wsOutput.Range(wsOutput.Cells(currentOutputRow, "A"), _
                          wsOutput.Cells(currentOutputRow + topInvestors - 1, "A")).Value = tickerCell.Value
            
            ' 更新输出起始行，准备下一个股票代码的数据
            currentOutputRow = currentOutputRow + topInvestors
        End If
    Next tickerCell
    
    MsgBox "股东数据提取完成！", vbInformation
End Sub

关键改进点说明 #

• 避免Select/ActiveCell操作：原代码频繁使用Select和ActiveCell，这是VBA中效率低且容易出错的操作。优化后的代码直接通过工作表对象（wsSource/wsOutput）操作单元格，稳定性和执行效率大幅提升。
• 明确指定输出工作表：直接将数据写入Sheet2，解决原代码无法输出到新工作表的问题。
• 批量填充股票代码：用Range.Resize的方式一次性填充对应行数的股票代码，替代原代码中的循环赋值，减少代码冗余并提升效率。
• 修正源数据范围获取错误：原代码中Cells(Rows.Count, 2)错误地引用了B列，改为Cells(wsSource.Rows.Count, "A")准确获取A列的最后一行数据。
• 动态参数配置：topInvestors变量可以直接修改，轻松调整需要提取的股东数量，满足动态设置需求。
• 清空旧数据：在开始提取前清空Sheet2的旧数据，避免数据残留导致的混乱。

内容的提问来源于stack exchange，提问作者Biteshock