VB Code to replicate action in any cell - Please, I need your help

[Acadia14]

Hello All,

I am new to vb. I need to accomplish a task urgently, and I am not sure anyone around me knows how, hence I have come to this forum.

I need to code a macro, which copies a cell above and pastes it below, then deletes cell values from N to U and instead pastes N through R and U with the following lookup formulae. Problem is it is very specific to the cell I recorded the macro in. How do I make this work in any cell, when I run the macro?

Sub Macro3()
'
' Macro3 Macro
'
'
Rows("174:174").Select
Range("M174").Activate
Selection.Copy
Rows("175:175").Select
Range("M175").Activate
ActiveSheet.Paste
Range("N175:U175").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("N175").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[6], R2C20:R163C23,3, FALSE), """")"
Range("N175").Select
Selection.AutoFill Destination:=Range("N175:R175"), Type:=xlFillDefault
Range("N175:R175").Select
Range("O175").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[5], R2C20:R163C23,4, FALSE), """")"
Range("P175").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[4], R2C20:R163C23,2, FALSE), """")"
Range("Q175").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[3], R2C20:R163C23,1, FALSE), """")"
Range("R175").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[2], R2C20:R163C23,5, FALSE), """")"
Range("U175").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC19=""Remove"",RC18,IF(RC19=""Add"",VLOOKUP(RC[-1],R2C19:R163C23,3),"" ""))"
Range("E176").Select
End Sub

Please, I need your assistance, as I am unable to figure it out.
hope someone can answer.

thanking you

 

[samuelimtech]

Hi, if im honest i cba rewriting your whole code but instead of using a cell reference use the offset function to say basically copy 1 cell up. something like activecell.offset(-1,0).copy activecell.offset(1,0).paste.
that will then copy from 1 cell 1 row above your acitve cell and paste it 1 row below.