VBA Edit to Search Range

[Alex O]

Hello Experts,
I'm still relatively new to VBA, and need some assistance with what I hope is a minor tweak. I'd like for my code below to search a range instead of a specific cell. I'm fairly certain that to do so the output (returned values) will also have to be tweaked - is this possible?

Sub Return_Results_Sheet()
searchValue = Range("A6")
searchCol = 40
returnValueCol = 37
outputValueCol = 8
outputValueRowStart = 6
lastRow = Cells(Rows.Count, searchCol).End(xlUp).Row
Range(Cells(outputValueRowStart, outputValueCol), Cells(Rows.Count, outputValueCol)).Clear
For i = 1 To lastRow
    checkValue = Cells(i, searchCol).Value
    If checkValue = searchValue Then
        returnvalue = Cells(i, returnValueCol)
        nextOutputRow = Cells(Rows.Count, outputValueCol).End(xlUp).Row + 1
        If nextOutputRow < outputValueRowStart Then
            nextOutputRow = outputValueRowStart
        End If
        Cells(nextOutputRow, outputValueCol).Value = returnvalue
    End If
Next i
End Sub

[p45cal]

It currently does search a range, but I guess you want to search that range for a number of different things. I'm going to assume things in cells A6:A8:

Sub Return_Results_Sheet()
searchValues = Range("A6:A8")  'your range of things being sought.
searchCol = 40
returnValueCol = 37
outputValueCol = 8
outputValueRowStart = 6
lastRow = Cells(Rows.Count, searchCol).End(xlUp).Row
Range(Cells(outputValueRowStart, outputValueCol), Cells(Rows.Count, outputValueCol)).Clear
For i = 1 To lastRow
  For Each searchValue In searchValues
    checkValue = Cells(i, searchCol).Value
    If checkValue = searchValue Then
      returnvalue = Cells(i, returnValueCol)
      nextOutputRow = Cells(Rows.Count, outputValueCol).End(xlUp).Row + 1
      If nextOutputRow < outputValueRowStart Then
        nextOutputRow = outputValueRowStart
      End If
      Cells(nextOutputRow, outputValueCol).Value = returnvalue
      'Cells(nextOutputRow, outputValueCol).Offset(, 1).Value = searchValue
    End If
  Next searchValue
Next i
End Sub

There's a commented-out line which if you enable will give the item being sought in the column to the right of the results column.

[Alex O]

Exactly what I was needing done...thanks!