PDA

View Full Version : [SOLVED] VBA Edit to Search Range



Alex O
01-29-2016, 08:37 AM
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
01-29-2016, 09:00 AM
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
01-29-2016, 09:45 AM
Exactly what I was needing done...thanks!