VBA-Excel查找并选择多个单元格 [英] VBA-Excel find and select multiple cells

问题描述

我正在写一个代码，我被困在这个问题上，我觉得不应该太难解决，但我不管它。

我需要我的程序来查找具有特定值的所有单元格并选择它们。但是，他们应该继续选择在子的结尾。
所以我更改了一个我在网上找到的代码，并写道：

  Sub FindAll（）
与工作表（4）.Range（a1：l500）
设置c = .Find（myValue，LookIn：= xlValues）
如果不是c不是然后
 firstAddress = c.Address 
 Do 
工作表（4）.Range（c.Address）.Activate 
设置c = .FindNext（c）
 
循环但不是c是没有和c.Address<> firstAddress 
 End If 
 End With 
 End Sub 
  

当然它依次选择它们，但是它们不会被选中，所以最后我只是选择了最后一个找到的单元格

任何人都可以帮我解决吗？
提前感谢

解决方案

使用联合方法将范围收集到一个不连续的范围，然后。选择他们离开子

  Sub FindAll（）
 Dim firstAddress As String，c As Range， rALL As Range 
与工作表（4）.Range（a1：l500）
设置c = .Find（myValue，LookIn：= xlValues）
如果不是c不是
设置rALL = c 
 firstAddress = c.Address 
 Do 
设置rALL = Union（rALL，c）
工作表（4）.Range（c.Address） 。激活
设置c = .FindNext（c）
 
循环，而不是c是没有，c.Address<> firstAddress 
 End If 
 .Activate 
如果不是rALL，则不是rALL.Select 
 End with 
 End Sub 
  

i'm writing a code and i'm stuck on this problem which i think should not bee too difficult to solve but i don't manage it.

I need my program to find all cells with a particular value and select them. But they should remain selected at the end of the sub. So i changed a bit a code i found on the web and wrote that:

Sub FindAll()
 With Worksheets(4).Range("a1:l500")
 Set c = .Find("myValue", LookIn:=xlValues)
 If Not c Is Nothing Then
    firstAddress = c.Address
    Do
        Worksheets(4).Range(c.Address).Activate   
        Set c = .FindNext(c)

    Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub

Of course it selects them in sequence but they do not remain selected, so at the end i just have the last found cell selected

Can anyone help me solve that? Thanks in advance

解决方案

Use the Union method to collect the ranges into one discontiguous range then .Select them before leaving the sub

Sub FindAll()
    Dim firstAddress As String, c As Range, rALL As Range
    With Worksheets(4).Range("a1:l500")
        Set c = .Find("myValue", LookIn:=xlValues)
        If Not c Is Nothing Then
            Set rALL = c
            firstAddress = c.Address
            Do
                Set rALL = Union(rALL, c)
                Worksheets(4).Range(c.Address).Activate
                Set c = .FindNext(c)

            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
        .Activate
        If Not rALL Is Nothing Then rALL.Select
    End With
End Sub

这篇关于VBA-Excel查找并选择多个单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！