| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to apply absolute cell referencing to multiple cells at once? | Excel Worksheet Functions | |||
| How can I apply a function (PROPER) to multiple cells at once? | Excel Worksheet Functions | |||
| apply arithmetic functions on special multiple cells | Excel Worksheet Functions | |||
| apply a formula to multiple cells | Excel Discussion (Misc queries) | |||
| How do I apply a combo box to multiple cells in Excel so that it . | Excel Discussion (Misc queries) | |||
Hybrid Mode
