在 VBA 中使用 Find 函数

Using the Find Function in VBA

提问by gabriel

1
2
3
4  
.
.

So I have a sequence of numbers running from 1-20. I have the number "1" on top selected and I would like to search the entire column and find the number "9". The code works when I don't name the range "rng"; it finds the number and selects. But the code stops working when I name the range of number. What's wrong with the range function? could it be that if I define Dim rng as Rangethat when I later define the "Set rng="I cannot have the ".Select"or ".Copy"extension on the end?

所以我有一个从 1 到 20 的数字序列。我选择了顶部的数字“1”，我想搜索整个列并找到数字“9”。当我没有将范围命名为“rng”时，代码有效；它找到号码并选择。但是当我命名数字范围时，代码停止工作。range 函数有什么问题？是不是如果我Dim rng as Range在以后定义时定义了，"Set rng="我就不能在最后有".Select"或".Copy"扩展名？

Sub macro2()

Dim rng As Range
Set rng = Range(ActiveCell, ActiveCell.End(xlDown)).Select
rng.Find(10).Select

End Sub

Also, If I want to sum the entire column from 1-20, on the last cell below the number "20" should I use the following code? because the application object doesn't seem to do it. Thank you!

另外，如果我想对 1-20 的整个列求和，在数字“20”下方的最后一个单元格上，我应该使用以下代码吗？因为应用程序对象似乎没有这样做。谢谢！

rng.End(xlDown).Offset(1, 0).Select
Application.WorksheetFunction.Sum (rng.Value)

采纳答案by brettdj

To look for 10 in the active column you could try this (which ends up selecting the first 10- although Selectin vbaisn't normally needed other than taken the user to location at code end)

要在活动列中查找 10，您可以尝试此操作（最终选择第一个10- 尽管Select在vba中通常不需要，除了将用户带到代码末尾的位置）

• test that the found range exists (ie you can find 10before proceeding)
• you should also use xlWholeto avoid matching 100if the current default for [lookAt]is xlPart
• using search [After]as Cells(1, ActiveCell.Column, and [Search Direction]as xlNextfinds the first value looking down.

• 测试找到的范围是否存在（即您可以10在继续之前找到）
• 你也应该使用xlWhole，以免匹配100，如果当前默认[的lookAt]是xlPart
• 使用 search [After]asCells(1, ActiveCell.Column和[Search Direction]asxlNext查找向下看的第一个值。

code

代码

Sub QuickFind()
Dim rng1 As Range
Set rng1 = ActiveCell.EntireColumn.Find(10, Cells(1, ActiveCell.Column), xlFormulas, xlWhole, , xlNext)
If Not rng1 Is Nothing Then
Application.Goto rng1
Else
MsgBox "10 not found"
End If
End Sub

Part 2

第2部分

Sub Other()
Dim rng1 As Range
Set rng1 = Range(Cells(1, ActiveCell.Column), Cells(Rows.Count, ActiveCell.Column).End(xlUp))
rng1.Cells(rng1.Cells.Count).Offset(1, 0) = Application.WorksheetFunction.Sum(rng1.Value)
End Sub

回答by Tanmay Nehete

Try this, I hope this will help u to find the specific row no as well as column name too. In code you can use

试试这个，我希望这也能帮助你找到特定的行号和列名。在代码中，您可以使用

strRw = FindColumn(Sheet name, "Value which need to be found", True, "Cell Name",Row number)
sourceCOL = colname(FindColumn(Shee Name, "Value which need to be found", False, , 4))

Below is main function of find

下面是 find 的主要功能

Public Function FindColumn(colnocountWS As Worksheet, srcstr As String, Optional rowflag As Boolean, Optional bycol As String, Optional strw As Integer, Optional stcol As Integer) As Integer
            Dim srcrng      As Range     'range of search text
            Dim srcAddr     As String  'address of search text
            Dim stcolnm     As String

            colnocountWS.Activate
            If stcol <> 0 Then stcolnm = colname(stcol)
            If stcol = 0 Then stcolnm = "A"
            If strw = 0 Then strw = 1


            colnocountWS.Range(stcolnm & strw).Select

            If ActiveSheet.Range(stcolnm & strw) = srcstr Then
                ActiveSheet.Range(stcolnm & strw).Select
                FindColumn = 1
            Else
            If bycol = "" Then
                Set srcrng = colnocountWS.Cells.Find(Trim(srcstr), after:=ActiveCell, LookIn:=xlValues _
                , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
            Else
                Set srcrng = colnocountWS.Cells.Find(Trim(srcstr), after:=ActiveCell, LookIn:=xlValues _
                , LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
            End If
            'ByPart
            If srcrng Is Nothing Then
                If bycol = "" Then
                    Set srcrng = colnocountWS.Cells.Find(Trim(srcstr), after:=ActiveCell, LookIn:=xlValues _
                    , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)
                Else
                    Set srcrng = colnocountWS.Cells.Find(Trim(srcstr), after:=ActiveCell, LookIn:=xlValues _
                    , LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)
                End If
            End If

                If srcrng Is Nothing Then
                FindColumn = 0
                 Exit Function
                Else
                    srcAddr = srcrng.Address
                    colnocountWS.Range(srcAddr).Select
                    FindColumn = ActiveCell.Column
                    If rowflag = True Then FindColumn = ActiveCell.Row
                End If

            End If
        End Function

        'this function find column name
        Public Function colname(iFinalCol1 As Integer) As String
        Dim colnm As String
        On Error GoTo gg
        If Mid(Cells(1, iFinalCol1).Address, 3, 1) = "$" Then
                colnm = Mid(Cells(1, iFinalCol1).Address, 2, 1)
          Else
                colnm = Mid(Cells(1, iFinalCol1).Address, 2, 2)
        End If
        gg: colname = colnm

        End Function