| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
Optimize simple macro
Hi folks!
I have this simple macro that was created using the recorder. Sub foo() Rows("2:2").Select Selection.Insert Shift:=xlDown Range("G2").Select ActiveCell.FormulaR1C1 = "=RC[-5]&RC[-4]&RC[-3]&RC[-2]&RC[-1]" Range("A1").Select End Sub It simply inserts a new row 2 and places a concatenation formula in cell G2. How can this be optimized? I know SELECTS are undesireable but I'm more interested in how to get the formula in A1 reference style. Thanks! Biff |
|
#2
|
|||
|
|||
|
modify to suit your cell addresses
Sub foo1() Rows(2).Insert Cells(2, "g").Formula = "=a2&a3&a4" End Sub -- Don Guillett SalesAid Software "Biff" wrote in message ... Hi folks! I have this simple macro that was created using the recorder. Sub foo() Rows("2:2").Select Selection.Insert Shift:=xlDown Range("G2").Select ActiveCell.FormulaR1C1 = "=RC[-5]&RC[-4]&RC[-3]&RC[-2]&RC[-1]" Range("A1").Select End Sub It simply inserts a new row 2 and places a concatenation formula in cell G2. How can this be optimized? I know SELECTS are undesireable but I'm more interested in how to get the formula in A1 reference style. Thanks! Biff |
|
#3
|
|||
|
|||
|
Thanks, Don.
Biff "Don Guillett" wrote in message ... modify to suit your cell addresses Sub foo1() Rows(2).Insert Cells(2, "g").Formula = "=a2&a3&a4" End Sub -- Don Guillett SalesAid Software "Biff" wrote in message ... Hi folks! I have this simple macro that was created using the recorder. Sub foo() Rows("2:2").Select Selection.Insert Shift:=xlDown Range("G2").Select ActiveCell.FormulaR1C1 = "=RC[-5]&RC[-4]&RC[-3]&RC[-2]&RC[-1]" Range("A1").Select End Sub It simply inserts a new row 2 and places a concatenation formula in cell G2. How can this be optimized? I know SELECTS are undesireable but I'm more interested in how to get the formula in A1 reference style. Thanks! Biff |
|
#5
|
|||
|
|||
|
Try
ActiveCell.Formula = "=B2&C2&D2&E2&F2" Regards, Peo Sjoblom "Biff" wrote: Hi folks! I have this simple macro that was created using the recorder. Sub foo() Rows("2:2").Select Selection.Insert Shift:=xlDown Range("G2").Select ActiveCell.FormulaR1C1 = "=RC[-5]&RC[-4]&RC[-3]&RC[-2]&RC[-1]" Range("A1").Select End Sub It simply inserts a new row 2 and places a concatenation formula in cell G2. How can this be optimized? I know SELECTS are undesireable but I'm more interested in how to get the formula in A1 reference style. Thanks! Biff |
|
#6
|
|||
|
|||
|
Thanks, Peo.
Biff "Peo Sjoblom" wrote in message ... Try ActiveCell.Formula = "=B2&C2&D2&E2&F2" Regards, Peo Sjoblom "Biff" wrote: Hi folks! I have this simple macro that was created using the recorder. Sub foo() Rows("2:2").Select Selection.Insert Shift:=xlDown Range("G2").Select ActiveCell.FormulaR1C1 = "=RC[-5]&RC[-4]&RC[-3]&RC[-2]&RC[-1]" Range("A1").Select End Sub It simply inserts a new row 2 and places a concatenation formula in cell G2. How can this be optimized? I know SELECTS are undesireable but I'm more interested in how to get the formula in A1 reference style. Thanks! Biff |
|
#7
|
|||
|
|||
|
interested in how to get the formula in A1 reference style.
Would this idea work? HTH :) Sub foo() Rows(2).Insert Range("G2").Formula = "=B2&C2&D2&E2&F2" Range("A1").Select End Sub -- Dana DeLouis Win XP & Office 2003 "Biff" wrote in message ... Hi folks! I have this simple macro that was created using the recorder. Sub foo() Rows("2:2").Select Selection.Insert Shift:=xlDown Range("G2").Select ActiveCell.FormulaR1C1 = "=RC[-5]&RC[-4]&RC[-3]&RC[-2]&RC[-1]" Range("A1").Select End Sub It simply inserts a new row 2 and places a concatenation formula in cell G2. How can this be optimized? I know SELECTS are undesireable but I'm more interested in how to get the formula in A1 reference style. Thanks! Biff |
|
#8
|
|||
|
|||
|
Thanks, Dana.
One of these days I'll get into VBA! Biff "Dana DeLouis" wrote in message ... interested in how to get the formula in A1 reference style. Would this idea work? HTH :) Sub foo() Rows(2).Insert Range("G2").Formula = "=B2&C2&D2&E2&F2" Range("A1").Select End Sub -- Dana DeLouis Win XP & Office 2003 "Biff" wrote in message ... Hi folks! I have this simple macro that was created using the recorder. Sub foo() Rows("2:2").Select Selection.Insert Shift:=xlDown Range("G2").Select ActiveCell.FormulaR1C1 = "=RC[-5]&RC[-4]&RC[-3]&RC[-2]&RC[-1]" Range("A1").Select End Sub It simply inserts a new row 2 and places a concatenation formula in cell G2. How can this be optimized? I know SELECTS are undesireable but I'm more interested in how to get the formula in A1 reference style. Thanks! Biff |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need Help With A Very Simple Macro | Excel Discussion (Misc queries) | |||
| Simple Hyperlink Macro | New Users to Excel | |||
| Date macro | Excel Discussion (Misc queries) | |||
| New Excel user needs help with simple Macro... | New Users to Excel | |||
| Can't get simple macro to run | Excel Worksheet Functions | |||
Hybrid Mode
