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

Acadia14

New Member
Joined
Jan 28, 2014
Messages
2
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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

samuelimtech

New Member
Joined
Jan 13, 2014
Messages
13
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,157
Latest member
MrBJBones

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top