Use Macro Record to insert formulas

[javelinmania]

I am trying to use Macro Record to reformat a spreadsheet. In it, I need to insert a column and then paste a formula into each row. I tried to use the Goto last cell function and the using the cursor go the proper column and then insert my formula and then past to the top.

Unfortunately, when I look at the code created, it has hardcode cell addresses (row and column). That won't work the next time when I have a different number of rows.

So how can I insert a formula in line two and then copy and past it to all rows using Macro Record? I can edit the generated macro if it isn't too difficult. Everything else I need to do works - just this one issue.

 

[jbeaucaire]

First see how many rows of data there are and store that number in a variable. I typically look UP column A to the last used row. Then insert your formula in your entire range all at once.

Dim LR as Long

LR = Range("A" & Rows.Count).End(xlUp).Row

Columns(4).Insert xlShiftToRight

Range("D2:D" & LR).FormulaR1C1 = "your formula goes here"

 

[javelinmania]

Thanks for the reply. Your solution worked and solved my problem. I appreciate the help.

 

[dadomi]

I am trying to do the same thing but it seems my formula function is too long. Is there any way to incorporate these formulas into my module as a macro?
To be input in Column AA1

 <!--[if gte mso 9]><xml>  <w:WordDocument>   <w:View>Normal</w:View>   <w:Zoom>0</w:Zoom>   <w:Compatibility>    <w:BreakWrappedTables/>    <w:SnapToGridInCell/>    <w:WrapTextWithPunct/>    <w:UseAsianBreakRules/>   </w:Compatibility>   <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel>  </w:WordDocument> </xml><![endif]--><!--[if gte mso 10]> <style>  /* Style Definitions */  table.MsoNormalTable     {mso-style-name:"Table Normal";     mso-tstyle-rowband-size:0;     mso-tstyle-colband-size:0;     mso-style-noshow:yes;     mso-style-parent:"";     mso-padding-alt:0in 5.4pt 0in 5.4pt;     mso-para-margin:0in;     mso-para-margin-bottom:.0001pt;     mso-pagination:widow-orphan;     font-size:10.0pt;     font-family:"Times New Roman";} </style> <![endif]-->  =IF(AND(ISBLANK(J1),ISBLANK(K1)),FALSE,IF(J1=1,ISERROR(MATCH(K1,[ErrorMacro.xls]Sheet1!$K$3:$K$1000,0)),IF(J1=2,ISERROR(MATCH(K1,[ErrorMacro.xls]Sheet1!$L$3:$L$1000,0)),IF(J1=3,ISERROR(MATCH(K1,[ErrorMacro.xls]Sheet1!$M$3:$M$1000,0)),TRUE))))

then add this conditional formatting in Column J1

 =$AA1

Next,
To be input in Column AB1

=IF(AND(OR($J1=1,$J1=2),ISBLANK($M1)),TRUE,FALSE

then add these conditional formats in Column M

 =OR(ISNUMBER(M1),$W1)
AND  =$AB1

 

[jbeaucaire]

Take out that final ,TRUE) and the formula should work.