I shall be thankful if any one helps me to create a macro or so..to solve the
following:

I have hunderes of cells with 8 digits decimals. When I click a maco the
cell to be updated as:

Presnt date: 1520.12565415
Desired Result: =1520.125+0

Thanks for the attentino.

John Britto

select your cells and run:

Sub Macro1()
Dim r As Range
For Each r In Selection
r.Value = Round(r.Value, 4) & "+0"
Next
End Sub

--
Gary''s Student


"John Britto" wrote:

I shall be thankful if any one helps me to create a macro or so..to solve the
following:

I have hunderes of cells with 8 digits decimals. When I click a maco the
cell to be updated as:

Presnt date: 1520.12565415
Desired Result: =1520.125+0

Thanks for the attentino.

John Britto

One way:

Public Sub ConstantsToFormulae()
Const sTEMPLATE As String = "=$$+0"
Dim rTargets As Range
Dim rCell As Range
On Error Resume Next 'in case no selected constants
Set rTargets = Selection.SpecialCells( _
xlCellTypeConstants, xlNumbers)
On Error GoTo 0
If Not rTargets Is Nothing Then
For Each rCell In rTargets
With rCell
.Formula = Replace(sTEMPLATE, "$$", _
Format(Int(.Value * 1000) / 1000, "0.000"))
End With
Next rCell
End If
End Sub


Select the cells to be changed and run the macro.

If you want this to be compatible with XL97 and MacXL versions, change
"Replace(" to "Application.Substitute("




In article ,
"John Britto" wrote:

I shall be thankful if any one helps me to create a macro or so..to solve the
following:

I have hunderes of cells with 8 digits decimals. When I click a maco the
cell to be updated as:

Presnt date: 1520.12565415
Desired Result: =1520.125+0

Thanks for the attentino.

John Britto

That's not what the OP wrote that he wanted.

With 1520.12565415 in a selected cell, this macro will result in a string

1520.126+0

when the OP's specification was

=1520.125+0



In article ,
Gary''s Student wrote:

select your cells and run:

Sub Macro1()
Dim r As Range
For Each r In Selection
r.Value = Round(r.Value, 4) & "+0"
Next
End Sub

--
Gary''s Student


"John Britto" wrote:

I shall be thankful if any one helps me to create a macro or so..to solve
the
following:

I have hunderes of cells with 8 digits decimals. When I click a maco the
cell to be updated as:

Presnt date: 1520.12565415
Desired Result: =1520.125+0

Better:

Public Sub ConstantsToFormulae()
Const sTEMPLATE As String = "=$$+0"
Dim rTargets As Range
Dim rCell As Range
With Selection
If .Count = 1 Then
If IsNumeric(.Value) Then _
Set rTargets = .Cells
Else
On Error Resume Next 'in case no selected constants
Set rTargets = .SpecialCells( _
xlCellTypeConstants, xlNumbers)
On Error GoTo 0
End If
End With
If Not rTargets Is Nothing Then
For Each rCell In rTargets
With rCell
.Formula = Replace(sTEMPLATE, "$$", _
Int(.Value * 1000) / 1000)
End With
Next rCell
End If
End Sub


Since SpecialCells returns all cells in the Used Range if only one cell
is selected, this modification will prevent overwriting all constants in
that case.

Also, since XL will parse the formula and ignore the format if there are
fewer than three significant figures after the decimal point, there's no
reason to use Format()

In article ,
JE McGimpsey wrote:

One way:

Dear,

Thanks for your prompt action.

I succeeded with the following one recommended by Garys Student. Since I
am not even a novice in VB codes I didnt try the codes of JE McGimpsey.

By the way, I tried not to round the value but couldnt succeed. Example:
1.524524 must be as 1.524.



Thanks a lot to you all,


John Britto




Sub CellEdit()
'
' CellEdit Macro
' Macro recorded 15-10-2005 by John Britto
'
Dim r As Range
For Each r In Selection
r.Value = "=" & Round(r.Value, 3) & "+0"
Next
End Sub


"JE McGimpsey" wrote:

One way:

Public Sub ConstantsToFormulae()
Const sTEMPLATE As String = "=$$+0"
Dim rTargets As Range
Dim rCell As Range
On Error Resume Next 'in case no selected constants
Set rTargets = Selection.SpecialCells( _
xlCellTypeConstants, xlNumbers)
On Error GoTo 0
If Not rTargets Is Nothing Then
For Each rCell In rTargets
With rCell
.Formula = Replace(sTEMPLATE, "$$", _
Format(Int(.Value * 1000) / 1000, "0.000"))
End With
Next rCell
End If
End Sub


Select the cells to be changed and run the macro.

If you want this to be compatible with XL97 and MacXL versions, change
"Replace(" to "Application.Substitute("




In article ,
"John Britto" wrote:

I shall be thankful if any one helps me to create a macro or so..to solve the
following:

I have hunderes of cells with 8 digits decimals. When I click a maco the
cell to be updated as:

Presnt date: 1520.12565415
Desired Result: =1520.125+0

Thanks for the attentino.

John Britto

Dear,

Thanks for your prompt action.

I succeeded with the following one recommended by Garys Student. Since I
am not even a novice in VB codes I didnt try the codes of JE McGimpsey.

By the way, I tried not to round the value but couldnt succeed. Example:
1.524524 must be as 1.524.



Thanks a lot to you all,


John Britto




Sub CellEdit()
'
' CellEdit Macro
' Macro recorded 15-10-2005 by John Britto
'
Dim r As Range
For Each r In Selection
r.Value = "=" & Round(r.Value, 3) & "+0"
Next
End Sub


"Gary''s Student" wrote:

select your cells and run:

Sub Macro1()
Dim r As Range
For Each r In Selection
r.Value = Round(r.Value, 4) & "+0"
Next
End Sub

--
Gary''s Student


"John Britto" wrote:

I shall be thankful if any one helps me to create a macro or so..to solve the
following:

I have hunderes of cells with 8 digits decimals. When I click a maco the
cell to be updated as:

Presnt date: 1520.12565415
Desired Result: =1520.125+0

Thanks for the attentino.

John Britto