Excel Query (find all + value of another cell in relation to results)

Question

I need to search ALL cells for a value (VALUE A) and then output the value of all cells 7 cells above that contains the value (VALUE A).

For example, if I search for V01 in the screenshot attached, I want to see the results "Crème Fraîche 326 µg" and "Cheese (Cow's Milk) 224 µg AVERAGE"

Screenshot

Please let me know if my answer helped you, or if you need additional information. — Justin Edwards, Oct 24 '22 at 21:32

score 0 · Answer 1 · answered Oct 23 '22 at 04:30

Here is an example that should be easily adaptable to your usage case:

Sub messageAssembler()
    Dim message As String
    Dim startingLocation As String
    Dim searchLocation As Range
    Dim foundLocation As Range
    Dim cellCount As Range
    Dim searchValue As String
    searchValue = "V01"
    Application.ScreenUpdating = False
    Range("A1").Select
    Set searchLocation = ActiveSheet.UsedRange
    Set cellCount = searchLocation.Cells(searchLocation.Cells.Count)
    Set foundLocation = searchLocation.Find(what:=searchValue, after:=cellCount)
    If Not foundLocation Is Nothing Then
        startingLocation = foundLocation.Address
        foundLocation.Activate
        ActiveCell.Offset(-7, 0).Select
        message = Chr(34) & ActiveCell & Chr(34)
        foundLocation.Activate
    Else
        GoTo noInstances
    End If
    Do Until foundLocation Is Nothing
        Set foundLocation = searchLocation.FindNext(after:=foundLocation)
        foundLocation.Activate
        ActiveCell.Offset(-7, 0).Select
        If foundLocation.Address <> startingLocation Then
            message = message & " and " & Chr(34) & ActiveCell & Chr(34)
        Else: Exit Do
        End If
    Loop
    Application.ScreenUpdating = True
    MsgBox message, vbOKOnly, "Search Result:"
Exit Sub
noInstances:
    MsgBox "The requested search value does not exist", vbOKOnly, "Error"
End Sub

The code looks for the value that has been assigned to the string "searchValue"
If any instance of the assigned value is found, it begins assembling a message based on the cell that is 7 cells above the instance. It then loops through to locate all of the other instances, and adds the cells that are 7 above each instance to the message, seperating them with " and ".
Once completed, the result is displayed in a message box.

When the preceding macro is ran on the example spreadsheet you provided with the searchValue set to "V01" this is the result:

Excel Query (find all + value of another cell in relation to results)

1 Answers1