VBA在Excel中搜索特定列

I am trying to search a specific column in Excel (Column K) using the below VBA code but when I run the macro it instead searches the whole sheet instead of the specified column.

我正在尝试使用下面的VBA代码搜索Excel(列K)中的特定列,但是当我运行宏时,它会搜索整个工作表而不是指定的列。

The problem is it firstly finds 'mycell1' in an earlier column, i.e. in Column C instead of Column K which I don't want it to do.

问题是它首先在较早的列中找到'mycell1',即在C列而不是列K中,我不希望它这样做。

I have also tried using 'xlByRows' in the 'Searchorder' which had the same issue.

我也尝试在'Searchorder'中使用'xlByRows',它有同样的问题。

Would greatly appreciate any help please

非常感谢任何帮助

Thanks

Range("K:K").Select
Set foundcell1a = Selection.Cells.Find(What:=mycell1, After:=ActiveCell, LookIn:=xlValues, LookAt _
    :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False)
Set foundcell1a = Cells.FindNext
If Not foundcell1a Is Nothing Then
foundcell1a.Activate
End If

2 个解决方案

#1

Don't use .Select if you can possibly avoid it and most of the time, you can.

不要使用。如果可以避免,请选择。大多数时候,你可以。

Try using With...End With constructs instead. Be as specific as you can with the object you want to operate on.

尝试使用With ... End With构造。尽可能使用您想要操作的对象。

Sub SearchK()
Dim mycell1
Dim foundcell1a As Range

    mycell1 = 1
    With ActiveWorkbook.Worksheets("Sheet1").Range("K:K")
        Set foundcell1a = .Find(mycell1, .Cells(.Rows.Count, 1))
        Set foundcell1a = .FindNext(foundcell1a)
        If Not foundcell1a Is Nothing Then
            foundcell1a.Activate
        End If
    End With
End Sub

Without a With...End With, you would have to repeat all the object identifiers so:

如果没有With ... End With,则必须重复所有对象标识符,以便:

 Set foundcell1a = .Find(mycell1, .Cells(.Rows.Count, 1))

Would have to be expressed as:

必须表达为:

Set foundcell1a = ActiveWorkbook.Worksheets("Sheet1").Range("K:K").Find(mycell1, .Cells(.Rows.Count, 1))

When VBA is evaluating a command it needs to evaluate each property preceding a period (.) every time it encounters it. Using ActiveWorkbook.Worksheets("Sheet1").Range("K:K") gets rid of 4 periods so it runs faster too.

当VBA评估命令时,它需要在每次遇到它时评估句点(。)之前的每个属性。使用ActiveWorkbook.Worksheets(“Sheet1”)。范围(“K:K”)除去4个句点,因此它也运行得更快。

The Set foundcell1a = .Find(mycell1, .Cells(.Rows.Count, 1)) is saying find mycell1 after the last used cell in column K so it loops back to find the first instance in column K regardless of the active cell.

Set foundcell1a = .Find(mycell1,.Cells(.Rows.Count,1))说在列K中最后一个使用的单元格之后找到mycell1,因此无论活动单元格如何,它都会循环返回以查找列K中的第一个实例。

#2

Try this:

With Range("K:K")
    Set LastCell = .Cells(.Cells.Count)
End With

Set FoundCell1a = Range("K:K").Find(mycell1, LastCell)
If Not FoundCell1a Is Nothing Then
    FirstAddr = FoundCell1a.Address
    Range(FirstAddr).Activate
End If

Hope this helps!

希望这可以帮助!

#1