Better way to apply formula to multiple cells?
 

Hi all,

First post, long time reader...

I've pasted some code below that does the following steps:
1.) Specifies a formula to combine two cells values into one
2.) Copy that formula and apply it to remaining records
3.) Convert the formula to a value (so that I can delete the source
rows)

This code works - but is there a sexier way to code this?

Thanks everyone!

- Brian



'Combine two column values for first record
Range("E4").Select
ActiveCell.FormulaR1C1 = "=TRIM(RC[-2]) & "" "" & RC[-1]"

'Copy formula and paste to additional records
Range("E4").Select
Selection.Copy
Range("E5:E17").Select
ActiveSheet.Paste

'Copy and paste special formulas as values
Range("E4:E17").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


--
bntringa
------------------------------------------------------------------------
bntringa's Profile: http://www.excelforum.com/member.php...o&userid=30523
View this thread: http://www.excelforum.com/showthread...hreadid=501753

Better way to apply formula to multiple cells?
 

Depneds what you maen by sexy <G

With Range("E5:E17")
.FormulaR1C1 = "=TRIM(RC[-2])&"" ""&RC[-1]"
.Value = .Value
End With


HTH

Bob

"bntringa" wrote in
message ...

Hi all,

First post, long time reader...

I've pasted some code below that does the following steps:
1.) Specifies a formula to combine two cells values into one
2.) Copy that formula and apply it to remaining records
3.) Convert the formula to a value (so that I can delete the source
rows)

This code works - but is there a sexier way to code this?

Thanks everyone!

- Brian



'Combine two column values for first record
Range("E4").Select
ActiveCell.FormulaR1C1 = "=TRIM(RC[-2]) & "" "" & RC[-1]"

'Copy formula and paste to additional records
Range("E4").Select
Selection.Copy
Range("E5:E17").Select
ActiveSheet.Paste

'Copy and paste special formulas as values
Range("E4:E17").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


--
bntringa
------------------------------------------------------------------------
bntringa's Profile:
http://www.excelforum.com/member.php...o&userid=30523
View this thread: http://www.excelforum.com/showthread...hreadid=501753

Better way to apply formula to multiple cells?
 

Hey that IS sexy!

Thanks for the help Bob!


--
bntringa
------------------------------------------------------------------------
bntringa's Profile: http://www.excelforum.com/member.php...o&userid=30523
View this thread: http://www.excelforum.com/showthread...hreadid=501753

Better way to apply formula to multiple cells?
 

Hey that IS sexy!

Thanks for the help Bob!


--
bntringa
------------------------------------------------------------------------
bntringa's Profile: http://www.excelforum.com/member.php...o&userid=30523
View this thread: http://www.excelforum.com/showthread...hreadid=501753

Better way to apply formula to multiple cells?
 

pity my spelling isn't <vbg

--
HTH

RP
"bntringa" wrote in
message ...

Hey that IS sexy!

Thanks for the help Bob!


--
bntringa
------------------------------------------------------------------------
bntringa's Profile:
http://www.excelforum.com/member.php...o&userid=30523
View this thread: http://www.excelforum.com/showthread...hreadid=501753