MacroHelp

[John Britto]

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

[Gary''s Student]

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

[JE McGimpsey]

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 <[email protected]>,
"John Britto" <[email protected]> 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

[JE McGimpsey]

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 <[email protected]>,
Gary''s Student <[email protected]> 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

[JE McGimpsey]

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 <[email protected]>,
JE McGimpsey <[email protected]> wrote:

> One way:

[John Britto]

Dear,

Thanks for your prompt action.

I succeeded with the following one recommended by Gary’s Student. Since I
am not even a novice in VB codes I didn’t try the codes of JE McGimpsey.

By the way, I tried not to round the value but couldn’t 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 <[email protected]>,
> "John Britto" <[email protected]> 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
>

[John Britto]

Dear,

Thanks for your prompt action.

I succeeded with the following one recommended by Gary’s Student. Since I
am not even a novice in VB codes I didn’t try the codes of JE McGimpsey.

By the way, I tried not to round the value but couldn’t 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