首页 文章

Excel VBA根据不同工作表中的动态单元格值选择和复制行数

提问于
浏览 次
0

在工作表1中，在单元格C3中，数值不断变化 . 我希望根据工作表1中的单元格值C3选择Sheet2中的行数 . 例如，Sheet2有100行和7列 . 在sheet1中，如果C2 = X且C3 = 5，我希望vba循环通过sheet2列A找到X的第一个匹配单元格，然后从第一个匹配中选择C3行数，并将它们复制到sheet3 .

Sheet1   Sheet2  Sheet3 (final result)
                  C2=X     A1=a    A3 row
                  C3=5     A2=b    A4 row
                           A3=X    A5 row
                           A4=X    A6 row
                           A5=X    A7 row
                           A6=X
                           A7=X
                           A8=X

这是我正在使用的代码：

Private Sub CommandButton1_Click()
    Worksheets("Sheet2").Activate
    a = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
    Worksheets("sheet1").Activate
    b = Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To a
        For j = 2 To b
            If Worksheets("Sheet2").Cells(i, 1).Value = Worksheets("Sheet1").Cells(j, 1).Value Then
                If Worksheets("Sheet1").Cells(j, 3).Value > Worksheets("Sheet1").Cells(j, 5).Value Then
                    'THE CODE I NEED HELP WITH
                End If
            End If
        Next j
    Next i
End Sub
excel vba excel-vba

1 回答

  • 0

    像这样的东西应该工作！

    Public Sub RunProgram()
Dim WSSheet1                                            As Worksheet
Dim WSSheet2                                            As Worksheet
Dim WSSheet3                                            As Worksheet
Dim ValueToFind                                         As String
Dim NumberRows                                          As Long
Dim LastRowToCheck                                      As Long
Dim CellIndex                                           As Range
Dim RngStart                                            As Long
Dim RngEnd                                              As Long

Set WSSheet1 = Sheets("Sheet1")

ValueToFind = WSSheet1.Range("C2")
NumberRows = WSSheet1.Range("C3")

Set WSSheet2 = Sheets("Sheet2")

LastRowToCheck = WSSheet2.Cells(WSSheet2.Rows.Count, 1).End(xlUp).Row

For Each CellIndex In WSSheet2.Range("A1:A" & LastRowToCheck)
    If CellIndex.Value = ValueToFind Then
        RngStart = CellIndex.Row
        RngEnd = RngStart + NumberRows
        Exit For
    End If
Next CellIndex

Set WSSheet3 = Sheets("Sheet3")

WSSheet3.Range("A1:A" & NumberRows).Value = WSSheet2.Range("A" & RngStart & ":A" & RngEnd).Value

End Sub
    回复于

相关问题