Solved: macro for every cell on the sheet

[Anomandaris]

Hi guys I need a macro that will change a number a certain way. It must be able to do this for any cell on the worksheet.

Basically it has to focus on the decimal of the number.
example if number is 203.2 then it should change that to 203.25
If it is 203.4 then it changes to 203.5
if it is 203.6 then change to 203.75

so 0.2 changes to 0.25
0.4 to 0.5
and 0.6 to 0.75

how do i do this? any ideas will be appreciated thanks a lot

[Anomandaris]

I tried to do this but it doesnt work



[VBA]Sub Calc()
Dim cell As Range
For Each cell In Sheet1
If cell.Value Like "*.2" Then
cell.Value = cell.Value + 0.05
If cell.Value Like "*.4" Then
cell.Value = cell.Value + 0.1
If cell.Value Like "*.6" Then
cell.Value = cell.Value + 0.15
End If
End If
End If
Next
End Sub[/VBA]

[Bob Phillips]

[vba]

Sub Calc()
Dim cell As Range
For Each cell In Sheet1.UsedRange

If Not cell.HasFormula Then

cell.Value = Application.Ceiling(cell.Value, 0.25)
End If
Next
End Sub
[/vba]

[mdmackillop]

Add this code to the worksheet module
[VBA]
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Fmla As String
Application.EnableEvents = False
If Target.Cells.Count > 1 Then Exit Sub
If Not Target.HasFormula Then
Target = Application.Round(Target * 4, 0) / 4
Else
Fmla = Application.Substitute(Target.Formula, "=", "")
Target.Formula = "=ROUND(4*(" & Fmla & "),0)/4"
End If
Application.EnableEvents = True
End Sub

[/VBA]

[mdmackillop]

I missed the Rounding up (well spotted Bob) so
[vba]
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Fmla As String
Application.EnableEvents = False
If Target.Cells.Count > 1 Then Exit Sub
If Not Target.HasFormula Then
Target = Application.Ceiling(Target, 0.25)
Else
Fmla = Application.Substitute(Target.Formula, "=", "")
Target.Formula = "=CEILING(" & Fmla & ",0.25)"
End If
Application.EnableEvents = True
End Sub
[/vba]

[Anomandaris]

Thanks again Xld you always come to my rescue!

It works as required.. btw man can you please take a look at one of my other problems, I'm trying to send email from excel via outlook, but through the outlook contact list. I'll re-post it without as much detail as before as I think i made it look longer and more complicated than it really is.

thanks again mate