☰

如何编写 VBA 代码来查找另一张表中的列标题的单元格，然后返回相应的列？

tag-icon • tag-icon microsoft-excel vba macros
如何编写 VBA 代码来查找另一张表中的列标题的单元格，然后返回相应的列？

因此，我有一个完整的电子表格，其中客户为列，产品为行，交叉单元格表示客户购买了多少件产品。在另一张表中，我希望能够运行一个宏，该宏将提取所有产品并列出单个客户的购买数量。

我有另一个宏，可以删除空白行，只留下客户已购买的产品。

答案1

使用如下数据：

在此处输入图片描述

运行此宏：

Sub prodList()
Dim cust As String, rLook As Range, msg As String
Dim ct As Variant, r As Range
cust = Application.InputBox(Prompt:="Enter Customer Name", Type:=2)
Set rLook = Range("1:1").Find(What:=cust, After:=Range("A1")).EntireColumn

msg = ""
For Each r In rLook.Cells
    pr = Cells(r.Row, 1).Value
    If pr = "" Then Exit For
    ct = CStr(r.Value)
    If ct <> "" Then
        msg = msg & vbCrLf & pr & vbTab & ct
    End If
Next r

MsgBox msg
End Sub

将显示：在此处输入图片描述

编辑＃1：

要将输出存储在工作表2使用这个宏：

Sub prodList2()
    Dim cust As String, rLook As Range, K As Long
    Dim ct As Variant, r As Range
    cust = Application.InputBox(Prompt:="Enter Customer Name", Type:=2)
    Set rLook = Range("1:1").Find(What:=cust, After:=Range("A1")).EntireColumn

    K = 1
    For Each r In rLook.Cells
        pr = Cells(r.Row, 1).Value
        If pr = "" Then Exit For
        ct = CStr(r.Value)
        If ct <> "" Then
            Sheets("Sheet2").Cells(K, 1) = pr
            Sheets("Sheet2").Cells(K, 2) = ct
            K = K + 1
        End If
    Next r
End Sub

相关内容