vba 如何添加到Excel VBA中的当前单元格选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25785628/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to add to current cell selection in Excel VBA
提问by Le Ish Man
So I have a loop that checks every cell in a column and finds a specific date (currently the Monday of the previous week). My code right now does select them correctly but I want it to keep the previous selection so in the end all cells of that specification are selected
所以我有一个循环来检查列中的每个单元格并找到一个特定的日期（当前是前一周的星期一）。我的代码现在确实正确选择了它们，但我希望它保留以前的选择，因此最终选择了该规范的所有单元格
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
As a bonus, to change the Function to a different day of last week would I simply change the vbMonday to vbTuesday etc? I admit I don't know VBA very well and most of this is just frankensteined from around here.
作为奖励，要将函数更改为上周的不同日期，我是否只需将 vbMonday 更改为 vbTuesday 等？我承认我不太了解 VBA，其中大部分只是来自这里的弗兰肯斯坦。
回答by Siddharth Rout
The best way to do that is to store all the cells in a range using the UnionMethod.
最好的方法是使用UnionMethod将所有单元格存储在一个范围内。
Also I wouldn't recommend using .Select. You may want to see THIS
另外我不建议使用.Select. 你可能想看看这个
Amend your code to add this code.
修改您的代码以添加此代码。
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
One more thing... Please note that xlDownshould be avoided as much as possible. You may want to see THIS
还有一件事...请注意，xlDown应尽可能避免。你可能想看看这个
