Optimize simple macro

[Biff]

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

 

[Don Guillett]

modify to suit your cell addresses
Sub foo1()
Rows(2).Insert
Cells(2, "g").Formula = "=a2&a3&a4"
End Sub

 

[Biff]

Thanks, Don.

Biff

modify to suit your cell addresses
Sub foo1()
Rows(2).Insert
Cells(2, "g").Formula = "=a2&a3&a4"
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)

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

 

[Guest]

Try

ActiveCell.Formula = "=B2&C2&D2&E2&F2"


Regards,

Peo Sjoblom

 

[Don Guillett]

glad it helped


--
Don Guillett
SalesAid Software
(e-mail address removed)

Thanks, Don.

Biff

modify to suit your cell addresses
Sub foo1()
Rows(2).Insert
Cells(2, "g").Formula = "=a2&a3&a4"
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)

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

 

[Dana DeLouis]

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

 

[Biff]

Thanks, Peo.

Biff

Try

ActiveCell.Formula = "=B2&C2&D2&E2&F2"


Regards,

Peo Sjoblom

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

 

[Biff]

Thanks, Dana.

One of these days I'll get into VBA!

Biff

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

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