如何将范围设置为特定的单元格 [英] how to set range to specific cells
问题描述
我的代码目前运行在第一列，并找到一个特定的关键字。我想在下一列中使用另一个关键字进行另一个搜索，但仅针对在第一列中找到该词的行。我想知道我该怎么做。
这是我的代码到目前为止：
设置aCell = oRange.Find（什么：= firstInput，LookIn：= xlValues，_
LookAt：= xlWhole，SearchOrder：= xlByRows，SearchDirection：= xlNext，_
MatchCase：= False ，SearchFormat：= False）
如果不是aCell是没有
设置bCell = aCell
FoundAt = aCell.Address
设置aCell = oRange.FindNext（After：= aCell）
如果不是aCell是没有
如果aCell.Address = bCell.Address然后
退出Do
FoundAt = FoundAt& ，& aCell.Address
Else
退出
结束如果
循环
其他
MsgBox SearchString& 找不到
退出子
结束如果
MsgBox搜索字符串已找到这些位置：& FoundAt
退出Sub
基本上所有在 FoundAt 成为下一列的搜索范围。
我不知道正是你正在坐在这个解决方案上，因为这听起来像是随着时间的推移而发生的问题。根据我所理解的问题，您想查找与您的搜索字词相匹配的列A中的所有行，然后仅搜索列B中不同搜索字词的行。所以，例如：
AB
Apple Cobbler
Cherry Cobbler
Peach Pie
Apple Pie
苹果iPad
如果您的2个搜索字词是Apple和 Pie，它将返回第4行。
我将使用Collection对象完成此操作。
Dim myCol As New Collection
Dim r As Integer
Dim FirstSearchTerm As String
Dim SecondSearchTerm As String
对于r = 1到Sheet1 .UsedRange.Rows.Count
如果Sheet1.Cells（r，1）= FirstSearchTerm然后
myCol.Add r，Sheet1.Cells（r，2）
End If
Next
MsgBox myCol.Item（SecondSearchTerm）'返回4
当然这也预示着苹果派不出现两次。例如：
AB
Apple Cobbler
Cherry Cobbler
Peach Pie
Apple Pie
苹果iPad
Apple Pie
将打破此代码。 >
编辑：回复评论
所以现在你想要搜索这样的东西？
ABCD
Apple Cobbler Joe Blow
Cherry Cobbler Joe DiMaggio
Peach Pie Sally麻雀
苹果派简·古多尔
苹果iPad Ivan可怕
苹果派萨莉麻雀
苹果派简西摩
当您有4个单独的搜索词Apple，Pie，Jane和Goodall时，可以返回第4行？
在这一点上，您可能需要考虑重组数据！ J / K：D
因此，我们不必将该单元格的值添加到Collection对象中，而是将一行添加到Collection对象中。然后我们必须继续循环通过我们的收藏，直到我们拥有高地人（只有一个！）实质上，我们继续玩弄，第四次筛选出坏的数据，直到我们只剩下好消息。我确定有一种方式可以递归地执行，但是它的5点。
Dim myFirstCol As New Collection
Dim mySecCol作为新集合
Dim x As Integer'切换到x，所以它不会混淆
Dim FirstSearchTerm As String
Dim SecondSearchTerm As String
Dim ThirdTerm As String
Dim FourthTerm As String
FirstSearchTerm =Apple
SecondSearchTerm =Pie
ThirdTerm =Jane
FourthTerm =Seymour
'首先，获取与我们的第一项匹配的所有行
对于x = 1 To Sheet1.UsedRange.Rows.Count
如果Sheet1.Cells（x，1）= FirstSearchTerm然后
myFirstCol.Add x'只保存行号
End If
Next
'现在循环通过该集合搜索我们的第二个项
'Item（x）contains与我们的搜索词匹配的行的行号
对于x = 1 To myFirstCol.Count
如果Sheet1.Cells（myFirstCol.Item（x），2）= SecondSearchTerm然后
mySecCol.Add myFirstCol.Item（x）
结束如果
下一个
'再次循环访问第三个项目
设置myFirstCol =新集合重新使用
对于x = 1 To mySecCol.Count
如果Sheet1.Cells（mySecCol.Item（x），3）= ThirdTerm Then
myFirstCol.Add mySecCol.Item（x）
End If
下一个
'和第四个
设置mySecCol =新集合重用它
对于x = 1到myFirstCol.Count
如果Sheet1.Cells（myFirstCol.Item （x），4）= FourthTerm然后
mySecCol.Add myFirstCol.Item（x）
如果
下一个
msgbox mySecCol.Item（1）'哪个行号符合我们所有的条款
My code currently runs through the first column and finds a certain key word. I want to do another search in the next column with another key word but only for the rows in which the word was found in the first column. I was wondering how I could do this.
Here's my code so far:
Set aCell = oRange.Find(What:=firstInput, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
Set bCell = aCell
FoundAt = aCell.Address
Do
Set aCell = oRange.FindNext(After:=aCell)
If Not aCell Is Nothing Then
If aCell.Address = bCell.Address Then
Exit Do
FoundAt = FoundAt & ", " & aCell.Address
Else
Exit Do
End If
Loop
Else
MsgBox SearchString & " not Found"
Exit Sub
End If
MsgBox "The Search String has been found these locations: " & FoundAt
Exit Sub
Basically all the locations returned in FoundAt become the search range for the next column.
I'm not sure where exactly you're sitting on a solution to this since it sounds like the question has evolved over time. Based on what I understand the question to be, you want to find all the rows in column A that match your search term and then ONLY search those rows in column B for a different search term. So, for example:
A B
Apple Cobbler
Cherry Cobbler
Peach Pie
Apple Pie
Apple iPad
If your 2 search terms were "Apple" and "Pie", it would return row 4.
I would accomplish this with a Collection object.
Dim myCol As New Collection
Dim r As Integer
Dim FirstSearchTerm As String
Dim SecondSearchTerm As String
For r = 1 To Sheet1.UsedRange.Rows.Count
If Sheet1.Cells(r, 1) = FirstSearchTerm Then
myCol.Add r, Sheet1.Cells(r, 2)
End If
Next
MsgBox myCol.Item(SecondSearchTerm) 'Returns 4
Of course, this also presupposes that Apple Pie does not appear twice. Eg:
A B
Apple Cobbler
Cherry Cobbler
Peach Pie
Apple Pie
Apple iPad
Apple Pie
Will break this code.
Edit: Response to the comment
So now you want to search something like this?
A B C D
Apple Cobbler Joe Blow
Cherry Cobbler Joe DiMaggio
Peach Pie Sally Sparrow
Apple Pie Jane Goodall
Apple iPad Ivan Terrible
Apple Pie Sally Sparrow
Apple Pie Jane Seymour
And be able to return row 4 when you have 4 separate search terms "Apple", "Pie", "Jane", and "Goodall"?
At this point you may want to think about restructuring your data! J/K :D
So, instead of adding the value of the cell to the Collection object, we'll add a Row to the Collection object. Then we have to keep looping through our Collection until we have the Highlander (There can be only one!) In essence, we keep juggling back and fourth sifting out the bad data until we are left with only the good. I'm sure there's a way to do this recursively, but its 5 o'clock.
Dim myFirstCol As New Collection
Dim mySecCol As New Collection
Dim x As Integer 'Switching to x so its not as confusing
Dim FirstSearchTerm As String
Dim SecondSearchTerm As String
Dim ThirdTerm As String
Dim FourthTerm As String
FirstSearchTerm = "Apple"
SecondSearchTerm = "Pie"
ThirdTerm = "Jane"
FourthTerm = "Seymour"
'First, get all rows matching our first term
For x = 1 To Sheet1.UsedRange.Rows.Count
If Sheet1.Cells(x, 1) = FirstSearchTerm Then
myFirstCol.Add x 'Just save the row number
End If
Next
'now loop through that collection search for our second term
'Item(x) contains the row number for rows that matched our search terms
For x = 1 To myFirstCol.Count
If Sheet1.Cells(myFirstCol.Item(x), 2) = SecondSearchTerm Then
mySecCol.Add myFirstCol.Item(x)
End If
Next
'And loop through again for the 3rd term
Set myFirstCol = New Collection 'Reuse it
For x = 1 To mySecCol.Count
If Sheet1.Cells(mySecCol.Item(x), 3) = ThirdTerm Then
myFirstCol.Add mySecCol.Item(x)
End If
Next
'And fourth
Set mySecCol = New Collection 'Reuse it
For x = 1 To myFirstCol.Count
If Sheet1.Cells(myFirstCol.Item(x), 4) = FourthTerm Then
mySecCol.Add myFirstCol.Item(x)
End If
Next
msgbox mySecCol.Item(1) 'Which is the row number matching all our terms
这篇关于如何将范围设置为特定的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
