PDA

View Full Version : Solved: macro for every cell on the sheet



Anomandaris
04-24-2009, 05:13 AM
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
04-24-2009, 05:28 AM
I tried to do this but it doesnt work



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

xld
04-24-2009, 05:33 AM
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

mdmackillop
04-24-2009, 05:40 AM
Add this code to the worksheet module

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

mdmackillop
04-24-2009, 05:47 AM
I missed the Rounding up (well spotted Bob) so

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

Anomandaris
04-24-2009, 05:48 AM
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