特定列的Excel VBA查找方法 [英] Excel VBA Find Method for specific column
本文介绍了特定列的Excel VBA查找方法的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
当我尝试在特定列中搜索值时,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屋!
查看全文