Results 1 to 12 of 12

20021112, 22:19 #1
 Join Date
 Jan 2001
 Location
 Melbourne, Victoria, Australia
 Posts
 201
 Thanks
 8
 Thanked 0 Times in 0 Posts
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

20021112, 22:53 #2
 Join Date
 Feb 2001
 Location
 Dublin, Ireland, Republic of
 Posts
 2,697
 Thanks
 1
 Thanked 0 Times in 0 Posts
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

20021113, 01:19 #3
 Join Date
 Jan 2001
 Location
 Melbourne, Victoria, Australia
 Posts
 201
 Thanks
 8
 Thanked 0 Times in 0 Posts
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

20021113, 09:48 #4
 Join Date
 Feb 2001
 Location
 Dublin, Ireland, Republic of
 Posts
 2,697
 Thanks
 1
 Thanked 0 Times in 0 Posts
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

20021113, 22:17 #5
 Join Date
 Jan 2001
 Location
 Melbourne, Victoria, Australia
 Posts
 201
 Thanks
 8
 Thanked 0 Times in 0 Posts
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

20021113, 23:51 #6
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 2 Times in 2 Posts
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 oneliner. Here's a complete macro that prompts for everything and does errorchecking. 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>
<font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
<small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

20021114, 03:39 #7
 Join Date
 Jan 2001
 Location
 Melbourne, Victoria, Australia
 Posts
 201
 Thanks
 8
 Thanked 0 Times in 0 Posts
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

20021114, 04:01 #8
 Join Date
 Jan 2001
 Location
 Melbourne, Victoria, Australia
 Posts
 201
 Thanks
 8
 Thanked 0 Times in 0 Posts
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

20021123, 01:42 #9
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,935
 Thanks
 6
 Thanked 9 Times in 7 Posts
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

20021124, 22:21 #10
 Join Date
 Jan 2001
 Location
 Melbourne, Victoria, Australia
 Posts
 201
 Thanks
 8
 Thanked 0 Times in 0 Posts
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

20021125, 03:43 #11
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,174
 Thanks
 2
 Thanked 454 Times in 373 Posts
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 reset both at the end, though (Application.ScreenUpdating = False and Application.Calculation = xlAutomatic). These measures can markedly speed up your macro.
CheersCheers,
Paul Edstein
[MS MVP  Word]

20021126, 10:46 #12
 Join Date
 Jan 2001
 Location
 Melbourne, Victoria, Australia
 Posts
 201
 Thanks
 8
 Thanked 0 Times in 0 Posts
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!