Search in a sheet for a query while cell equals specific value (VBA)

Question

I'm trying to write a code to search for a query in a column, while holding certain value in another column.

example : as long as any cell in column A = 100 , Then search for "Text 1" in column B

When this 100 becomes 200 then search for "Text 2" in column B.. and so on till the last row of the sheet.

Here is my code:

Sub SearchQuery()
    Dim i As Long
    For i = 2 To Sheets("New Sheet").Cells(Rows.Count, 1).End(xlUp).Row
        'Criteria search
        If Sheets("New Sheet").Cells(i, 1).Value = "40042710" Then
            If Sheets("New Sheet").Cells(i, 2).Value = "11:35:00 AM" Then
                  'Set the Cell value
            End If
        End If
    Next i
End Sub

Need a few more details to provide good answer to this: - Could you find the target value in a given column more than once and if so, would you keep looking for others or stop there? - Is your data range continuous or are you likely to have breaks (empty cells) in your column? - You have lots of data? Because writing/reading like this can be slow.... If so you may have have to use arrays — logicOnAbstractions, Dec 20 '15 at 19:44
Thanks for your attention, yes the given value can be repeated like 5 or 6 times (afterwards), then the next set of rows will hold a new value which will be repeated Also 5 or 6 times, and so on.... there is no empty cells. i have around 500 rows. — Se7sNator, Dec 20 '15 at 20:13

score 2 · Accepted Answer · answered Dec 20 '15 at 20:22

When there are only two directly linked values, the Scripting.Dictionary object can prove quite useful. The dictionary.Exists(key) function makes it simple to test for valid keys. The similar Scripting.Collection object lacks this functionality.

Here's a rough snippet showcasing how you might use it. Note that you'll need to set a reference to Microsoft Scripting Runtime or use late binding instead!

Sub Example()
    Dim keyRange As Range
    Dim valueRange As Range
    Dim keyCell As Range
    Dim valueCell As Range
    Dim searchKey As String
    Dim searchValue As String
    Dim noDuplicateSearches As Boolean: noDuplicateSearches = True
    Dim criteria As New Dictionary

    With criteria ' criteria as key-value pairs
        .Add "100", "Text 1"
        .Add "200", "Text 2"
    End With

    Set keyRange = Sheets(1).Columns(1).SpecialCells(xlCellTypeConstants)
    Set valueRange = Sheets(1).Columns(2).SpecialCells(xlCellTypeConstants)

    For Each keyCell In keyRange
        searchKey = keyCell.Value

        If criteria.Exists(searchKey) Then ' on a valid key
            searchValue = criteria(searchKey)

            For Each valueCell In valueRange
                If valueCell.Value = searchValue Then ' on a valid value
                    Debug.Print "Found", searchValue, _
                                "using", searchKey, _
                                "at", valueCell.Address
                End If
            Next valueCell

            If noDuplicateSearches Then criteria.Remove searchKey
        End If
    Next keyCell

End Sub

Of course, generating the entire criteria dictionary each time the sub is run is far from optimal. You could choose to use a different sub to initialize (or unload!) the dictionary in a public variable instead.

score 0 · Answer 2 · edited May 23 '17 at 12:06

I am making the assumption that you know exactly what values your search depends upon. I would search the column using something like the below link:

How to find a value in an excel column by vba code Cells.Find

Then I would follow that up with a Select Case to search again for whatever you desire.

The following link is to the MSDN Select Case page: https://msdn.microsoft.com/en-us/library/cy37t14y.aspx

Example: search column A for value 100. if exists then set the Cells.Find() parameters accordingly. If the value 100 is not found in column A then search for 200, etc.

Search in a sheet for a query while cell equals specific value (VBA)

2 Answers2