# Thread: Apply formula to Selected Cells (Excel 2K)

1. ## Apply formula to Selected Cells (Excel 2K)

Hi,

How can I create a macro to increment the value of cells in a selection containing a number or formula by a fixed amount, say 1.15 (15%). Blank cells and cells containing text should be ignored.
If the value of the cell is 1234 then after applying macro the cell should contain the formula

2. ## Re: Apply formula to Selected Cells (Excel 2K)

Something like the following :

Sub Multiply()
Dim oCell As Range
For Each oCell In Selection
If Left(oCell.Formula, 1) = "=" Then
oCell.Formula = "=(" & _
Right(oCell.Formula, _
Len(oCell.Formula) - 1) & ") * 1.15"
Else
oCell.Formula = "=(" & oCell.Formula & ") * 1.15"
End If
Next
End Sub

If your selection is A1:A3 and A3 = A1+A2, you could have problems as the multiplier would effectively be apllied twice to A3.

Andrew C

3. ## Re: Apply formula to Selected Cells (Excel 2K)

Andrew,

Thanks for your input.

With some minor mods to handle text in the second part it is now looking good.

<pre>Sub Apply115()
Dim oCell As Range
For Each oCell In Selection
If Left(oCell.Formula, 1) = "=" Then
oCell.Formula = "=(" & _
Right(oCell.Formula, _
Len(oCell.Formula) - 1) & ") * 1.15"
Else
If oCell.Value <> "" Then
If IsNumeric(oCell) Then
oCell.Formula = "=(" & oCell.Value & ") * 1.15"
End If
End If
End If
Next
End Sub
</pre>

My new query is that it takes a little while (a couple of seconds) when a column is selected, which is the way the user tends to work. Is there any way this can be further minimised by restricting it to the used area of the column or the spreadsheet, or is this happening already?

Thanks again and Regards,

Peter Moran

4. ## Re: Apply formula to Selected Cells (Excel 2K)

Try the following :<pre>Dim oCell As Range
Dim oSelect As Range
If TypeName(Selection) <> "Range" Then Exit Sub
Set oSelect = Intersect(Selection, ActiveSheet.UsedRange)
For Each oCell In oSelect
'..etc
</pre>

I included a line to cater for selections that not appropriate.

Andrew C

5. ## Re: Apply formula to Selected Cells (Excel 2K)

Andrew,

Thanks again for your input.

Will try your further suggestion and let you know result but it may not be today due to other commitments.

Regards,

Peter Moran

6. ## Re: Apply formula to Selected Cells (Excel 2K)

Don't forget that you can do this with a paste special, multiple, so it is really just a one-liner. Here's a complete macro that prompts for everything and does error-checking. HTH --Sam
<pre>Option Explicit
Sub Macro1()
Dim dblFactor As Double
Dim rngSave As Range, rngM As Range, rngEmpty As Range
Set rngSave = Selection ' Save selection
Set rngEmpty = rngSave.SpecialCells(xlCellTypeLastCell).Offset(1)
dblFactor = Application.InputBox(Prompt:="Enter factor", Type:=1)
On Error GoTo noRange
Set rngM = Application.InputBox(Prompt:="Select Range", _
Default:=rngSave.Address, Type:=8)
On Error GoTo 0
rngEmpty = dblFactor
rngEmpty.Copy
rngM.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
rngEmpty.Clear
rngSave.Select ' Restore selection
noRange:
Set rngM = Nothing
Set rngSave = Nothing
Set rngEmpty = Nothing
End Sub</pre>

7. ## Re: Apply formula to Selected Cells (Excel 2K)

Hi Sam,

Thanks for your input.

I noted a couple of points:

1. Blank cells in the selected range end up as 0. You may have a suggestion as to how this can be resolved.

2. The change to numbers does not leave a trace of what has happened. I like it where a number is wrapped with a formula so that the original can still be seen if required.

3. Full marks for flexibility, like what is in ASAP Utilities, but my user works with a fixed factor and wants it as simple as possible.

Regards,

Peter Moran

8. ## Re: Apply formula to Selected Cells (Excel 2K)

Andrew,

Managed to incorporate and test your changes and the result is spot on!!.

Many thanks.

Regards,

Peter Moran

9. ## Re: Apply formula to Selected Cells (Excel 2K)

Peter,

It may not apply to your case, but not all formulas are numeric computations. For example, what if the formula is something like
=CONCATENATE(...
or
=LEFT(...
or any other string formula (or a call to a UDF that returns a string).

Seems like the check for ISNUMERIC should come first and then test, if true, whether there's a formula in there.

Fred

10. ## Re: Apply formula to Selected Cells (Excel 2K)

Fred,

Thanks for your thoughts.

While I think it is unlikely, I have now included your numeric test as suggested as good practice.

Regards,

Peter Moran

11. ## Re: Apply formula to Selected Cells (Excel 2K)

Hi Peter,

Two other things that you should consider, especially if your macro is going to be applied to large ranges:
1) turn off screen updating (Application.ScreenUpdating = False); and
20 set calculation to manual (Application.Calculation = xlManual),
while the macro is running. Remember to re-set both at the end, though (Application.ScreenUpdating = False and Application.Calculation = xlAutomatic). These measures can markedly speed up your macro.

Cheers

12. ## Re: Apply formula to Selected Cells (Excel 2K)

Hi, Macropod,

Thanks for your good thoughts. I have now included them both in my macro.

Regards,

Peter Moran

Two heads are always better than one!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•