如何设置范围内特定细胞(how to set range to specific cells)

我的代码目前通过的第一列运行，并找到一个特定的关键词。我想这样做在下一列其他搜索与另一个关键字，但仅适用于有这个词在第一列中的行。我想知道我怎么能做到这一点。

这里是我到目前为止的代码：

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

基本上在返回的所有位置FoundAt成为下一列的搜索范围。

Answer 1:

我不知道你坐在这正是一个解决方案，因为它听起来像这个问题已经随着时间而演变。根据我了解的问题是，要找到所有在A列符合您的搜索词，然后只搜索在B列的那些行对不同的搜索项的行。因此，举例来说：

  A          B
Apple     Cobbler
Cherry    Cobbler
Peach     Pie
Apple     Pie
Apple     iPad

如果您2个检索词是“苹果”和“派”，它将返回的第4排。

我会用一个Collection对象做到这一点。

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

当然，这也预示着苹果馅饼不会出现两次。例如：

  A          B
Apple     Cobbler
Cherry    Cobbler
Peach     Pie
Apple     Pie
Apple     iPad
Apple     Pie

将打破这个代码。

编辑：回应评论

所以，现在你要搜索这样的事情？

  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

并能够当你有4个独立的搜索词“苹果”，“丿”，“简”和“古道尔”返回一行4？

在这一点上，你可能要考虑重组您的数据！ J / K：d

因此，而不是增加细胞对Collection对象的价值，我们将添加一行到收藏对象。然后，我们必须继续通过我们收集循环，直到我们有汉兰达（只能有一个！）从本质上说，我们生活奔波回来和第四筛选出坏的数据，直到我们剩下的只有良好。我敢肯定有一种方法可以递归地做到这一点，但它的5点。

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

Answer 2:

这个怎么样：

Sub test2()
Dim firstInput As String
Dim i As Integer, col As Integer
Dim Rng As Range
Dim check1 As Boolean

firstInput = "cat"
col = 1 ' this will start us off in column 1 ("A")

With Sheets("New") ' using this sheet
   ' There's no reason to loop through ALL columns in Excel, let's just use the columns that are actually in use (.usedrange.columns.count)
    For i = 1 To .UsedRange.Columns.Count 
        Do While Rng Is Nothing 'This will loop until a match is found, or not found
            If col > .UsedRange.Columns.Count Then Exit Do ' if we exceed the used columns, you can stop looking through them
             Set Rng = .Columns(col).Find(what:=firstInput, LookIn:=xlValues, lookat:=xlWhole, searchOrder:=xlByRows)
             col = col + 1
        Loop
    Next i


    If Rng Is Nothing Then
        MsgBox ("Nothing found in this sheet")
    Else         ' When there is a match, do below code
        MsgBox (firstInput & " found in cell " & Rng.address)
        Application.Goto Rng, True
        RowNum = Rng.row
        MsgBox RowNum
        check1 = True
    End If
End With

End Sub

这可能有一个或两个障碍，这取决于你的工作是如何设置的。只要让我知道如果这个工程，或者你如果没有什么错误。此外，任何问题，只是问！

编辑：呸 - 我看你是按行搜索。你需要，要这样的话，或者是上述好吗？

Answer 3:

@ user3242614说：

嗨，我在想，如果你能帮助我的另一个问题。我有“樱桃皮匠乔安娜”。我怎么能插入该行的数据到线2所以我存储在每个前三列到一个集合的，但我不知道什么检查，我可以在最后一列做决定，将其插入上述第2行。

所以，如果你有设置数据：

  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

而有人进入：

Cherry    Cobbler    Joe     Anna

你想搜索并输入字母呢？

如果是这样的话，则每个后For x = 1 to collection.Count .... Next循环，检查collection.Count的值。如果其零，他们已“搜索”的东西不存在，所以应该增加。

因此，第一个集合收集后，我们会插入此代码：

If myFirstCol.Count = 0 Then 'No values matched
    For r = 1 To Sheet1.UsedRange.Rows.Count
    'But for subsequent loops you need to loop through the Collection Like this:
    '  For r = 1 To myFirstCol.Count
        If Sheet1.Cells(r, 1) > FirstSearchTerm Then
            r = r - 1 ' The Row the data should be inserted into.
            Rows(r).Insert
            Cells(r, 1) = FirstSearchTerm
            Cells(r, 2) = SecondSearchTerm
            Cells(r, 3) = ThirdTerm
            Cells(r, 4) = FourthTerm
            Exit For 'Jump out..no sense doing more comparisons
        End If
    Next
End If

心连心

文章来源: how to set range to specific cells