特定列的Excel VBA查找方法 [英] Excel VBA Find Method for specific column

问题描述

当我尝试在特定列中搜索值时，VBA find方法似乎失败.这段代码

VBA find method seems to fail when I am trying to search for the value in specific column. This code

Sub TargetR()
Dim CLL As Range
Dim TargetRange As Worksheet
Dim R As Range

Set CLL = ThisWorkbook.Worksheets(1).Range("J29")
Set TargetRange = ThisWorkbook.Worksheets(1)
Set R = TargetRange.Cells.Find(CLL.Value)

If Not (R Is Nothing) Then
    Debug.Print R.Address
Else: Debug.Print "Empty"
End If

End Sub

完美运行.虽然由带有关键字标题的列限制的搜索失败:

works perfectly. While the search limited by the column with keyword header fails:

Sub Target()
Dim CLL As Range
Dim TargetRange As Worksheet
Dim targetColumn As Range
Dim sColumn As Range


Dim R As Range

Set CLL = ThisWorkbook.Worksheets(1).Range("J29")
Set TargetRange = ThisWorkbook.Worksheets(1)
Set sColumn = TargetRange.Cells.Find("This Column")
Set targetColumn = sColumn.EntireColumn
Set R = targetColumn.Cells.Find(CLL.Value)

If Not (R Is Nothing) Then
    Debug.Print R.Address
Else: Debug.Print "Empty"
End If

End Sub

通过xlByColunm指定搜索方向无济于事

Specifying search direction through xlByColunm does not help

推荐答案

尝试以下代码(代码内部的注释说明):

Try the code below (explanation inside the code as comments):

Option Explicit

Sub Target()

Dim CLL As Range
Dim TargetRange As Worksheet
Dim sColumn As Range
Dim R As Range

Set CLL = ThisWorkbook.Worksheets(1).Range("J29")
Set TargetRange = ThisWorkbook.Worksheets(1)

Set sColumn = TargetRange.Cells.Find("This Column")
If Not sColumn Is Nothing Then ' <-- make sure Find was successful
    Set R = sColumn.EntireColumn.Find(what:=CLL.Value, LookIn:=xlValues, lookat:=xlWhole)        
    If Not R Is Nothing Then
        Debug.Print R.Address
    Else: Debug.Print "Empty"
    End If
Else ' Find failed to find "This Column"
    MsgBox "Unable to find 'This Column'"
End If 

End Sub

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