excel - VBA 代码中的多个 IF 语句可根据另一列从多列中提取不同的单元格值。

Question

抱歉，如果以前有人问过这个问题，但我找不到与我已有的代码相匹配的解决方案，除了我添加的条件外，它几乎可以工作。

解释:

我在 Sheet1 中有多个记录 #s。我需要在 Sheet2 中找到相同的匹配项，当找到时，我需要它根据第 7 列中的值(以及随后的行号)返回第 8 列和第 15 列中的所有值。

例如:

   Sheet1:
        Column 1 
        123
        999
        989

Sheet2:
Column1   Column7   Column8      Column 15
321        PRA      PRAABC       Completed
123        IRA      IRABCD       Cancelled
000        TPSD     TPSDRST      Completed
989        APSD     APSDABC      In Prog

所以结果将是:

123 IRABCD 取消

第989章

我的代码如下:

Sub CopyBasedonSheet1()

Dim i As Long
Dim j As Long
Sheet1LastRow = Worksheets("Sheet1").Range("O" & Rows.Count).End(xlUp).Row
Sheet2LastRow = Worksheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row

    For j = 1 To Sheet1LastRow
        For i = 1 To Sheet2LastRow
            If Worksheets("Sheet1").Cells(j, 15).Value = Worksheets("Sheet2").Cells(i, 2).Value Then 'if Engagement # from sheet1 matches sheet2

                    If Worksheets("Sheet2").Cells(i, 7) = "IRA" Then
                        Worksheets("Sheet1").Cells(j, 23).Value = Worksheets("Sheet2").Cells(i, 8).Value 
                        Worksheets("Sheet1").Cells(j, 24).Value = Worksheets("Sheet2").Cells(i, 15).Value
                    If Worksheets("Sheet2").Cells(i, 7) = "TPSD" Then
                        Worksheets("Sheet1").Cells(j, 25).Value = Worksheets("Sheet2").Cells(i, 8).Value
                        Worksheets("Sheet1").Cells(j, 26).Value = Worksheets("Sheet2").Cells(i, 15).Value
                    ElseIf Worksheets("Sheet2").Cells(i, 7) = "CA" Then
                        Worksheets("Sheet1").Cells(j, 27).Value = Worksheets("Sheet2").Cells(i, 8).Value
                        Worksheets("Sheet1").Cells(j, 28).Value = Worksheets("Sheet2").Cells(i, 15).Value

            Else
            End If
    Next i
Next j
End Sub

我收到了 "Next without For" Next i 上的错误

Answer 1

这就是您的代码的简化版本。值得注意的变化是

声明工作表变量( ws1 和 ws2 )以减少您必须键入/读取字符串的次数 Worksheets("Sheet#")

切换自 ElseIf使用方法Select Case

更正了最后一行计算中的一些不合格对象

已添加 Option Explicit为清楚起见

谈到效率，您可能会更好地循环遍历数组而不是像这样的范围。无论哪种方式，切换 Screen Updating 是个好主意加快速度。

Option Explicit

Sub CopyBasedonSheet1()

Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Sheet1")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("Sheet2")

Dim i As Long, j As Long
Dim LRow1 As Long, LRow2 As Long

LRow1 = ws1.Range("O" & ws1.Rows.Count).End(xlUp).Row
LRow2 = ws2.Range("B" & ws2.Rows.Count).End(xlUp).Row

For j = 1 To LRow1
    For i = 1 To LRow2
        If ws1.Cells(j, 15).Value = ws2.Cells(i, 2).Value Then
            Select Case ws2.Cells(i, 7)
                Case "IRA"
                    ws1.Cells(j, 23).Value = ws2.Cells(i, 8).Value
                    ws1.Cells(j, 24).Value = ws2.Cells(i, 15).Value
                Case "TPSD"
                    ws1.Cells(j, 25).Value = ws2.Cells(i, 8).Value
                    ws1.Cells(j, 26).Value = ws2.Cells(i, 15).Value
                Case "CA"
                    ws1.Cells(j, 27).Value = ws2.Cells(i, 8).Value
                    ws1.Cells(j, 28).Value = ws2.Cells(i, 15).Value
            End Select
        End If
    Next i
Next j

End Sub