在特定列中使用范围查找方法 [英] Use Range Find Method in a specific column
问题描述
我一直在修补一段时间,我下面的代码正常工作,但搜索不止我想要的。我希望它只是搜索列C而不是整个工作表。我已经尝试将 Cells.Find
更改为 Range(C1:C10000)。查找
,但它不返回比赛。
这个真的很糟糕。
Dim r As范围
Set r = Sheets(State Agent List)。Range(C1:C10000)。Find(What:= ComboBox22.Value,_
After:= ActiveCell,LookAt := xlPart,SearchOrder:= xlByRows)
如果不是r没有,然后
r.Select
ActiveWindow.ScrollRow = ActiveCell.Row
Else
MsgBox位置未列出。
如果
您的问题是使用的 ActiveCell
如果恰好不在 C1:C10000
中将不会返回。尝试这样:
Dim searchRng As Range:Set searchRng = Sheets(State Agent List)。Range(C1:C10000 )
/ pre>
Dim r As Range
设置r = searchRng.Find(什么:= ComboBox22.Value,After:= searchRng(searchRng.Count)
参数
searchRng(searchRng.Count)
属于您正在工作的范围的最后一个单元格上。它的工作原理可以这样明确地写出来:searchRng.Cells(searchRng.Cells.Count)
为什么我们需要将
之后的
参数设置为最后一个单元格?
主要原因是搜索从第一个单元格开始。 HTHI have been tinkering with this for a while now, I have the below code working but it searches more than I would like it to. I would like it to just search column C and not the entire sheet. I have tried changing out the
Cells.Find
toRange("C1:C10000").Find
but it returns no matches.Really stumped on this one.
Dim r As Range Set r = Sheets("State Agent List").Range("C1:C10000").Find(What:=ComboBox22.Value, _ After:=ActiveCell, LookAt:=xlPart, SearchOrder:=xlByRows) If Not r Is Nothing Then r.Select ActiveWindow.ScrollRow = ActiveCell.Row Else MsgBox "Location not listed." End If
解决方案Your problem is the use of
ActiveCell
which if happens to be not withinC1:C10000
will return nothing. Try this:Dim searchRng As Range: Set searchRng = Sheets("State Agent List").Range("C1:C10000") Dim r As Range Set r = searchRng.Find(What:=ComboBox22.Value, After:=searchRng(searchRng.Count))
The argument
searchRng(searchRng.Count)
pertains to the last cell of the Range you're working on. It works but it can be written explicitly this way:searchRng.Cells(searchRng.Cells.Count)
Why do we need to set the
After
argument to the last cell?
Main reason is for the search to begin from the very first cell. HTH这篇关于在特定列中使用范围查找方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!