如何在Excel VBA中添加到当前单元格选择 [英] How to add to current cell selection in Excel VBA

问题描述

因此，我有一个循环，用于检查列中的每个单元格并查找特定日期(当前是上周的星期一).我的代码现在确实正确选择了它们，但我希望它保留先前的选择，因此最后选择了该规范的所有单元格

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

作为奖励，要将Function更改为上周的另一天，我是否只需将vbMonday更改为vbTuesday等?我承认我对VBA不太了解，其中大部分只是从这里开始的科学怪谈.

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.

推荐答案

最好的方法是使用Union方法将所有单元格存储在一个范围内.

The best way to do that is to store all the cells in a range using the Union Method.

我也不建议使用.Select.您可能需要查看这

Also I wouldn't recommend using .Select. You may want to see THIS

修改您的代码以添加此代码.

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

另一件事...请注意，应尽可能避免使用xlDown.您可能需要查看

One more thing... Please note that xlDown should be avoided as much as possible. You may want to see THIS

这篇关于如何在Excel VBA中添加到当前单元格选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！