Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apply formula to a range with multiple selections
I'm trying to apply a formula to a range that a user can select.
Basically I have a user form where the user can select a range of data to be processed as well as an output cell where there the converted/processed range is put. Once the input ranges and output cell has been chosen I need to apply a formula to every cell in the input range and put it in the output cell. I have been successful in doing this where the input range is only a single selection but have been unable to get it to work with more multiple selections as the input range. Here is my code: Dim r As Range Dim r2 As Range ' Input and output ranges in userform Set r = Range(refInput.Value) Set r2 = Range(refOutput.Value) r.Copy r2.PasteSpecial Paste:=xlPasteValues Selection.SpecialCells(xlConstants).Formula = "--- formula goes here ---" ' Get rid of formulas so user does not see them. Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues Any way to get my code to work with multiple selections. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apply formula to a range with multiple selections
Dim r As Range
Dim r2 As Range Dim r3 as Range Dim ar as Range ' Input and output ranges in userform Set r = Range(refInput.Value) Set r2 = Range(refOutput.Value) r.Copy r2.PasteSpecial Paste:=xlPasteValues r3 = Selection.SpecialCells(xlConstants) r3.Formula = "--- formula goes here ---" ' Get rid of formulas so user does not see them. for each ar in r3.Areas ar.Formula = ar.Value Next -- Regards, Tom Ogilvy wrote in message oups.com... I'm trying to apply a formula to a range that a user can select. Basically I have a user form where the user can select a range of data to be processed as well as an output cell where there the converted/processed range is put. Once the input ranges and output cell has been chosen I need to apply a formula to every cell in the input range and put it in the output cell. I have been successful in doing this where the input range is only a single selection but have been unable to get it to work with more multiple selections as the input range. Here is my code: Dim r As Range Dim r2 As Range ' Input and output ranges in userform Set r = Range(refInput.Value) Set r2 = Range(refOutput.Value) r.Copy r2.PasteSpecial Paste:=xlPasteValues Selection.SpecialCells(xlConstants).Formula = "--- formula goes here ---" ' Get rid of formulas so user does not see them. Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues Any way to get my code to work with multiple selections. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apply formula to a range with multiple selections
Sorry if it was not clear, the Input range the user specifies can
consist of multiple selectons. I'm going to test the code you guys gave and will give feedback if it works or not. Thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apply formula to a range with multiple selections
The code does not work as I gave the wrong details.
Oops, I left out the most important details of my query. The formula that needs to be applied to the input range is the following extremely long one. IF(OR(CELL("format",B2)="D9",(LEN(B2)-LEN(SUBSTITUTE(B2,":","")))=1),--("0"&B2)/60,--("0"&B2)) This needs to be applied to each cell in the input range, where B2 is for example in the input range. Selection.Applyformula or Range.Applyformula only works when the selection in continuous, but when the input range consists of multiple selections my code does not work as it the output range ends up with the wrong formula Sorry for the confusion but any help in getting the correct output would help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to get range names to Apply across multiple tabs | Excel Worksheet Functions | |||
How do I apply a formula to multiple rows at the same time? | Excel Worksheet Functions | |||
Apply autofilter selections to 2nd table automatically? Macro? | Excel Worksheet Functions | |||
Creating Multiple GIFs from Multiple Range selections -- I need a volunteer to test my code to see why it fails | Excel Programming | |||
apply a formula to multiple cells | Excel Discussion (Misc queries) |