Modify a Macro to Continue LOOKUP Search

[meppwc]

The macro below populates cells R4 and S4 of the “Summary” worksheet with data found on the “Lookup Tables” worksheet.
On “Summary” worksheet cell R4, lookup value in “Summary” cell O4 using lookup table on worksheet “Lookup Tables” cells A1 – A27 and once value is found return the value in that row found in column C. Here is what the formula would look like in cell R4 of the “Summary” worksheet: =LOOKUP(O4,'Lookup Tables'!$A$1:$A$27,'Lookup Tables'!$C$1:$C$27)

Then on “Summary” worksheet cell S4, lookup value in” Summary” cell O4 using lookup table on worksheet “Lookup Tables” cells A1 – A27 and once value is found return the value in that row found in column B. Here is what the formula would look like in cell S4 of the “Summary” worksheet: =LOOKUP(O4,'Lookup Tables'!$A$1:$A$27,'Lookup Tables'!$B$1:$B$27)

This macro works correctly for cells R4 and S4 of the “Summary” worksheet. But now I want the macro to continue doing lookups down to cells R50 and S50 of the “Summary” worksheet. If no value is present in column O of the “Summary” worksheet, then the lookup does not take place for that row.


Sub populatePOC()
'
' populatePOC Macro
'

'
Sheets("Summary").Select
Range("R4").Select
ActiveCell.FormulaR1C1 = _
"=LOOKUP(RC[-3],'Lookup Tables'!R[-3]C[-17]:R[23]C[-17],'Lookup Tables'!R[-3]C[-15]:R[23]C[-15])"
Range("S4").Select
ActiveCell.FormulaR1C1 = _
"=LOOKUP(RC[-4],'Lookup Tables'!R[-3]C[-18]:R[23]C[-18],'Lookup Tables'!R[-3]C[-17]:R[23]C[-17])"
Range("S5").Select
End Sub