首页 文章

Excel VBA - 在列中搜索特定条件并将该行复制到新工作表

提问于
浏览 次
0

我是VBA的新手，所以我不确定我的代码有什么问题，或者这是否是我想要的最佳方式 . 我有一个巨大的原始数据表， Headers 为“数据”，有很多列，其中一个是日期 . 首先，我希望宏创建四个名为spring，summer，fall和winter的新工作表，并将它们放在工作表结束时 . 然后我想在“数据”中搜索一个月的日期列，并将相应的行复制到与该月对应的季节工作表中 . 这就是我到目前为止 - 我的if if语句出错了 . 我将日期列定义为数组，但我不确定是否甚至需要定义它 . 但是，当使用此宏时，“data”中的行数将不会是常量 . 谢谢 .

Sub Copy_Sorted_Data()

    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Spring"
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Summer"
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Fall"
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Winter"

    Dim Data As Worksheet
    Dim Spring As Worksheet
    Dim Summer As Worksheet
    Dim Fall As Worksheet
    Dim Winter As Worksheet
    Dim Entered() As Date
    Dim size As Integer
    Dim i As Integer

    size = WorksheetFunction.CountA(Worksheets("Data").Columns(1))
    ReDim numbers(size)

    For i = 1 To size
        numbers(i) = Cells(i, 9).Value
    Next i

    If Entered = March Or April Or May Then ActiveSheet.Row.Value.Copy Destination:=Worksheets("Spring")
    If Entered = June Or July Or August Then ActiveSheet.Row.Value.Copy Destination:=Worksheets("Summer")
    If Entered = September Or October Or November Then ActiveSheet.Row.Value.Copy Destination:=Worksheets("Fall")
    If Entered = December Or January Or February Then ActiveSheet.Row.Value.Copy Destination:=Worksheets("Winter")

    End If
    End If
    End If
    End If
End Sub
excel-vba vba excel

1 回答

  • 0

    1 . 删除这些代码：

    If Entered = March Or April Or May Then ActiveSheet.Row.Value.Copy Destination:=Worksheets("Spring")
If Entered = June Or July Or August Then ActiveSheet.Row.Value.Copy Destination:=Worksheets("Summer")
If Entered = September Or October Or November Then ActiveSheet.Row.Value.Copy Destination:=Worksheets("Fall")
If Entered = December Or January Or February Then ActiveSheet.Row.Value.Copy Destination:=Worksheets("Winter")
End If
End If
End If
End If

    第2位 . 如果要从第9列读取数据，请从该列中找到 size ：

    size = WorksheetFunction.CountA(Worksheets("Data").Columns(9))

    3 . 当你想要找到你的结果时：

    Dim SpringRows as long
Dim Summer as long
Dim Fall as Long
Dim WinterRows as Long

For i = 1 To size
    numbers(i) = Cells(i, 9).Value
    'working with month numbers is better
    If (Month(numbers(i)) = 3) OR (Month(numbers(i)) = 4) OR (Month(numbers(i)) = 5) then 
        SpringRows = SpringRows + 1
        Worksheets("Spring").Cells(SpringRows, 1).Value = numbers(i)
    End If
    ' ... and so on, for others
Next i
    回复于

相关问题