MacroHelp

[Guest]

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

 

[Guest]

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

 

[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("

 

[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

 

[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()

 

[Guest]

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

 

[Guest]

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