View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
joeu2004 joeu2004 is offline
external usenet poster
 
Posts: 2,059
Default Additions of Round Function and Minus Sign at Once

PS ....

On Mar 1, 12:50 pm, I wrote:
On Mar 1, 2:28 am, Khalil wrote:
2...Addition of (- ) minus sign to rounded values


If you want to avoid results like =-ROUND(3,0) and if you literally
want to prefix "-" only to formulas where ROUND(...,0) was added (that
is, you do not want to change 3 to -3), the following might do a
better job for you.


Sub EditIt()
Dim form As String
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Selection
If Application.IsNumber(cell) Then
form = cell.formula
If Left(form,1) = "=" Or Int(cell) < cell Then
If Left(form,1) = "=" Then form = Right(form,Len(form)-1)
cell.formula = "=-round(" & form & ",0)"
End If
End If
Next
Application.ScreenUpdating = True
End Sub


If you want to change 3 to -3 as well, the following might work for
you.


Sub EditIt()
Dim form As String
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Selection
If Application.IsNumber(cell) Then
form = cell.formula
If Left(form, 1) = "=" Then
form = Right(form, Len(form) - 1)
cell.formula = "=-round(" & form & ",0)"
ElseIf Int(cell) < cell Then
cell.formula = "=-round(" & form & ",0)"
Else
cell.formula = "-" & form
End If
End If
Next
Application.ScreenUpdating = True
End Sub


----- original posting -----

On Mar 1, 12:50*pm, joeu2004 wrote:
On Mar 1, 2:28 am, Khalil wrote:

I need to do two things with this data


It sounds like you are asking for a way to edit formulas over a large
range without having to resort to manual steps. *In other words, you
want to change:

=expression

to

=-round(expression,0)

Select the cells to be changed (see comments below), then execute the
following macro:

Sub EditIt()
Dim form As String
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Selection
* *If Application.IsNumber(cell) Then
* * * form = cell.formula
* * * If Left(form,1) = "=" Then form = Right(form,Len(form)-1)
* * * cell.formula = "=-round(" & form & ",0)"
* *End If
Next
Application.ScreenUpdating = True
End Sub

To enter the macro, press alt-F11 to open the VBE, then click Insert
Module. *Copy-and-paste the text of the macro above into the window
that should open on the right. *Be sure the desired cells are selected
in the worksheet, then in the VBE, put the cursor within the macro and
press F5.

Some comments, if I may ....

You wrote:
Out of these around 4-5 hundred rows consist
decimals values.


I think you are trying to say that 400-500 cells display values that
have decimal fractions, whereas the remaining 400-500 cells appear to
have integer values.

The operative word is "appear". *If all of the cells have formulas,
not constants, the actual value might not be exactly an integer value,
even if it appears to be an integer when formatted to the maximum
number of decimal places for 15 "significant" digits.

For example, the value 3.01 might appear as 3.0 because of cell
formatting. *And the value 3+2^-51 will appear as "3." followed by 14
zeros, but its internal representation is not identical to 3. *The
latter may or may not cause problems in some circumstances.

So it is prudent to round all formulas that might result in non-
integer values with the some numbers. *For example, if the formula is
=A1/A2, and that results in exactly 2 only because A1 is 16 and A2 is
8, it would be more robust and prudent to change the formula to =round
(A1/A2,0) so that you get the desired result even if you change A1 or
A2.

For this reason, I suggest that you apply the editing macro above to
all 900 cells.

----- original posting -----

On Mar 1, 2:28*am, Khalil wrote:



I have a data than contains more than 900 rows. All these data contain
two function / and *. *Out of these around 4-5 hundred rows consist
decimals values.
I need to do two things with this data


1... Addition of round function to *decimals values simultaneously
without using copy/Paste.


2...Addition of (- ) minus sign to rounded values at once without
using copy/Paste.


How *couple of these things possible *please advise


Thanks in advance


Khalil