在Excel列中查找所有实例 [英] Find All Instances in Excel Column

问题描述

上下文

我试图在特定的列中搜索"January"的所有实例，然后引用该行，以便可以将单元格直接复制到它的左侧.单元格包含公式，因此我实际上是在搜索单元格的值而不是公式.

I am trying to search in a specific column for all instances of "January" and then reference the row so I can copy the cell directly to the left of it. The cells contain formulas so I am actually searching for the Value of the cell not the formula.

问题

我一直在尝试进行测试，并确保它引用了正确的单元格地址(特别是该行).对于一月的每个实例，它将"Testing"粘贴到该列中的所有值的末尾，而不是粘贴在FoundCell本身中.(例如，如果1月出现2次，它将在该列的末尾连续循环测试")

I have been trying to test out and make sure it is referencing the right cell address (specifically, the row). For each instance of January, it pastes "Testing" at the end of all values in the column instead of in FoundCell itself. (ex. if January appeared 2 times, it would continuously loop "Testing" at the end of the column)

Excel示例

Month      Month
January    =A2
February   =A3
April      =A4
April      =A5
January    =A6
December   =A7

当前代码

Dim fnd As String, FirstFound As String
Dim FoundCell As Range, rng As Range
Dim myRange As Range, LastCell As Range

Set myRange = Range("B:B")
Set LastCell = myRange.Cells(myRange.Cells.Count)
Set FoundCell = myRange.Find(what:=January, after:=LastCell, LookIn:=xlValues)

'Test to see if anything was found
If Not FoundCell Is Nothing Then
  FirstFound = FoundCell.Address
Do
    MsgBox "Found it!"
    FoundCell.Value = "Testing"
    Set FoundCell = myRange.FindNext(FoundCell)
  Loop While Not FoundCell Is Nothing And FoundCell.Address <> FirstFound
Else
 MsgBox "Not Found!"
End If

Set rng = FoundCell

Exit Sub

请让我知道您是否有什么可以帮助您的！

Please let me know if there is anything you see that could help!

推荐答案

通过一些修改，我可以使您的代码运行.不确定要执行的操作是什么，但是基于测试数据，该代码不再遇到在 Loop While :

With some modification, I am able to get your code to run. Not sure exactly what you're trying to do, but based on the test data this code no longer hits the Runtime 91 error that your code encountered at the Loop While:

Sub foo()
Dim fnd As String, FirstFound As String
Dim FoundCell As Range, rng As Range
Dim myRange As Range, LastCell As Range

Set myRange = Range("B:B")
Set LastCell = myRange.Cells(myRange.Cells.Count)
Set FoundCell = myRange.Find(what:="January", after:=LastCell, LookIn:=xlValues)

If Not FoundCell Is Nothing Then
    FirstFound = FoundCell.Address
    Do

        ' FoundCell.Interior.ColorIndex = 39
        FoundCell.Offset(,1).Value = "Testing!"  '<~~ I'm writing out to the adjacent cell, just to be safe. Modify as needed.
        Set FoundCell = myRange.FindNext(FoundCell)

    Loop While (FoundCell.Address <> FirstFound)
End If

Set rng = FoundCell  '<~~ Careful, as this is only the LAST instance of FoundCell.

End Sub

这篇关于在Excel列中查找所有实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！