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
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