Apply formula to a range with multiple selections

[deekaye]

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.

 

[Tom Ogilvy]

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

 

[JE McGimpsey]

One way:

Can't tell exactly how your input and output ranges relate to your
Selection, but this should work:

Dim rInput As Range
Dim rOutput As Range
Dim rArea As Range
Dim rConstants As Range

Set rInput = Range(refInput.Value)
Set rOutput = Range(refOutput.Value)

With rInput
rOutput.Resize(.Rows.Count, .Columns.Count).Value = .Value
End With

For Each rArea In Selection.Areas
On Error Resume Next 'In case no constants in area
Set rConstants = rArea.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not rConstants Is Nothing Then
With rConstants
.Formula = "=""hello"""
.Value = .Value
End With
End If
Next rArea

 

[deekaye]

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.

 

[deekaye]

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.