因此,我有一个循环检查列中的每个单元格并找到特定日期(当前是前一周的星期一)。我的代码现在确实正确选择了它们,但我希望它保留之前的选择,以便最终选择该规范的所有单元格
Public Function LastMonday(pdat As Date) As Date
LastMonday = DateAdd("ww", -1, pdat - (Weekday(pdat, vbMonday) - 1))
End Function
Sub Macro2()
Macro2 Macro
Dim rng As Range
Dim curCellValue As String
Dim mondayStr As String
mondayStr = Format(LastMonday(Date), "dd/mm/yyyy")
Set rng = Range(ActiveSheet.Range("E2"), ActiveSheet.Range("E2").End(xlDown))
For Each Cell In rng
curCellValue = Cell.Value
If curCellValue = mondayStr Then Cell.Select
Next Cell
End Sub
作为奖励,要将函数更改为上周的另一天,我是否只需将 vbMonday 更改为 vbTuesday 等?我承认我不太了解 VBA,其中大部分内容都是来自这里的弗兰肯斯坦。
最好的方法是使用以下命令将所有单元格存储在一个范围内Union
Method.
我也不建议使用.Select
。您可能想看看THIS https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros/10718179#10718179
修改您的代码以添加此代码。
Dim MySel As Range
For Each cell In Rng
If cell.Value = mondayStr Then
If MySel Is Nothing Then
Set MySel = cell
Else
Set MySel = Union(MySel, cell)
End If
End If
Next cell
If Not MySel Is Nothing Then
With MySel
'.Select
'~~> Do something
End With
End If
还有一件事...请注意xlDown
应尽可能避免。您可能想看看THIS https://stackoverflow.com/questions/25759464/find-and-replace-a-word-from-excel-application-defined-or-object-defined-error/25760089#25760089
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)