尝试搜索月/年列表（excel vba）

笨蛋

我正在使用以下代码尝试查找一个月 +“Sales”+ 年的所有实例，然后删除该行。但是，我收到了不匹配错误。我不认为我的 .Find 函数可以接受列表的参数。我尝试在我的循环中使用循环来循环遍历列表中的所有变量，但仍然出现不匹配错误。关于如何解决这个问题的任何想法？谢谢：

'Add months and years for other possibilities
Dim mymonth() As String
Dim myyear() As String
mymonth = Split("September, October, November, December, January, February, March, April, May, June, July, August", ",")
myyear = Split("2014, 2015, 2016, 2017, 2018, 2019, 2020", ",")

'now check
Set SrchRng = ActiveSheet.Range("C1", ActiveSheet.Range("C65536").End(xlUp))
Do
    Set c = SrchRng.Find(mymonth + " Sales " + myyear, LookIn:=xlValues)
    If Not c Is Nothing Then c.EntireRow.Delete
Loop While Not c Is Nothing

如果您能找到一种更好的方法以 yyyy 格式查找任何年份，而不是手动列出它们，那就再好不过了。我确定我可以使用 date 函数来实现这一点，但我很难得到它只需要一个简单的列表......

虎化身

这样的事情应该适合你：

Sub tgr()

    Dim ws As Worksheet
    Dim rSearch As Range
    Dim rFound As Range
    Dim rDel As Range
    Dim aYears() As Variant
    Dim aMonths() As Variant
    Dim vYear As Variant
    Dim vMonth As Variant
    Dim sTemp As String
    Dim sFirst As String

    Set ws = ActiveWorkbook.ActiveSheet
    Set rSearch = ws.Columns("C").EntireColumn
    aMonths = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
    aYears = Array(2014, 2015, 2016, 2017, 2018, 2019, 2020)

    For Each vYear In aYears
        For Each vMonth In aMonths
            sTemp = vMonth & " Sales " & vYear
            Set rFound = Nothing
            Set rFound = rSearch.Find(sTemp, rSearch.Cells(rSearch.Cells.Count), xlValues, xlPart, , xlNext, False)
            If Not rFound Is Nothing Then
                sFirst = rFound.Address
                If rDel Is Nothing Then Set rDel = rFound Else Set rDel = Union(rDel, rFound)
                Do
                    Set rDel = Union(rDel, rFound)
                    Set rFound = rSearch.FindNext(rFound)
                Loop While rFound.Address <> sFirst
            End If
        Next vMonth
    Next vYear

    If Not rDel Is Nothing Then rDel.EntireRow.Delete

End Sub

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。