Excel宏在多列中查找-Java 学习之路

我有两个工作表“帐户”和“模拟” . 在第一张表中，我有A到L列和1409行 . 在第二张表中，用户在单元格B4上输入值 . 我必须在第一张表中的A1：A1409范围内搜索此值 . 如果在A100中找到该值，则A100至L100将被分配给第二张中的单元格B8：B11，B16：B19和B22：B25 .

如果在A1：A1409范围内没有找到，那么我必须在E1：E1409范围内搜索并再次将A100返回L100到B8：第二张中的B11，B16：B19和B22：B25 .

尝试创建一个excel 2010 vba宏来完成此任务 . 任何帮助表示赞赏 .

第一张

enter image description here

第二张

Second Sheet

此致，Ragav .

1 回答

What happens in this code:

从"Mock Up!B4"获取搜索值
在"Accounts"上搜索A列
如果找到匹配项，请将数据从"Mock Up"复制到"Accounts"（行），与提供的图片相匹配 .
如果在"A"中找不到匹配项，请再次搜索"E"
如果在"E"上找到匹配项，则将数据从"Accounts"（行）复制到"Mock Up"

Code:

Sub MockUpTranfer()
Dim lastRow As Long, lRow As Long
Dim source As String, target As String, tempVal As String
Dim match As Boolean

    match = False
    source = "Mock up"
    target = "Accounts"

    'Get last Row of target Sheet and temp value to search.
    lastRow = Sheets(target).Range("A" & Rows.count).End(xlUp).row
    tempVal = Sheets(source).Range("B4")

    'Check the search value against Column A on "Accounts"
    For lRow = 1 To lastRow
        'Copy from MockUp to Accounts
        If Sheets(target).Cells(lRow, "A") = tempVal Then
            Sheets(target).Cells(lRow, "B") = Sheets(source).Range("B10")
            Sheets(target).Cells(lRow, "C") = Sheets(source).Range("B8")
            Sheets(target).Cells(lRow, "D") = Sheets(source).Range("B9")
            Sheets(target).Cells(lRow, "E") = Sheets(source).Range("B19")
            Sheets(target).Cells(lRow, "F") = Sheets(source).Range("B18")
            Sheets(target).Cells(lRow, "G") = Sheets(source).Range("B17")
            Sheets(target).Cells(lRow, "H") = Sheets(source).Range("B16")
            Sheets(target).Cells(lRow, "I") = Sheets(source).Range("B22")
            Sheets(target).Cells(lRow, "J") = Sheets(source).Range("B23")
            Sheets(target).Cells(lRow, "K") = Sheets(source).Range("B24")
            Sheets(target).Cells(lRow, "L") = Sheets(source).Range("B25")
            match = True
        End If
    Next lRow

    'No match found in "A", now searching "E"
    If match = False Then
        For lRow = 1 To lastRow
            'Copy from Accounts to MockUp
            If Sheets(target).Cells(lRow, "E") = tempVal Then
                Sheets(source).Range("B10") = Sheets(target).Cells(lRow, "B")
                Sheets(source).Range("B8") = Sheets(target).Cells(lRow, "C")
                Sheets(source).Range("B9") = Sheets(target).Cells(lRow, "D")
                Sheets(source).Range("B19") = Sheets(target).Cells(lRow, "E")
                Sheets(source).Range("B18") = Sheets(target).Cells(lRow, "F")
                Sheets(source).Range("B17") = Sheets(target).Cells(lRow, "G")
                Sheets(source).Range("B16") = Sheets(target).Cells(lRow, "H")
                Sheets(source).Range("B22") = Sheets(target).Cells(lRow, "I")
                Sheets(source).Range("B23") = Sheets(target).Cells(lRow, "J")
                Sheets(source).Range("B24") = Sheets(target).Cells(lRow, "K")
                Sheets(source).Range("B25") = Sheets(target).Cells(lRow, "L")
            End If
        Next lRow
    End If
End Sub

note: 如果存在多个匹配，则最后一个匹配将覆盖第一个匹配 . 这将遍历整个帐户表 .

回复于 2024-04-29T12:19:43+08:00

Excel宏在多列中查找

1 回答

相关问题