MRound VBA formula setting all cell values to zero

[Mikeymike_W]

Hi All,

Thanks in advance for any help you can provide.

I have a formula in VBA which rounds the values in a range to the nearest 0.5.
It works fine but it also sets all the cell values to zero if they are blank, does anyone know a way to get past this?

My code is:

For i = 5 To 20
Sheets(1).Cells(i, 7).Value = Application.MRound(Sheets(1).Cells(i, 7).Value, 0.25)
Next

I have this located in my active sheet under worksheet_change

Thanks again,

Mike

 

[sheetspread]

For i = 5 To 20
with Sheets(1).Cells(i, 7)
if .value <> "" then
.value = Application.MRound(.Value, 0.25)
end if
end with
Next

 

[Mikeymike_W]

Hi,

This worked a treat thanks.

Quick question, is it also possible to incorporate an iferror function that would leave the cell blank and open a msgbox?

Thanks,

Mike

 

[sheetspread]

For i = 5 To 20
with Sheets(1).Cells(i, 7)
if .value <> "" then
.value = Application.MRound(.Value, 0.25)
else
msgbox "message"
end if
end with
Next

 

[Mikeymike_W]

Hi,

Thanks again for your help.

Unfortunately it didn't really work properly, the error message shows whenever a value is entered and you need to click ok about five times to get rid of the msgbox.

Any ideas?

 

[sheetspread]

If you just want the box to appear once:

Sub test()
Dim i%, x%
x = 0
For i = 5 To 20
With Sheets(1).Cells(i, 7)
If .Value <> "" Then
.Value = Application.MRound(.Value, 0.25)
Else
x = x + 1
End If
End With
Next
If x > 0 Then
MsgBox "message"
End If
End Sub

is one way, or add a countif formula for fewer steps.

 

[Mikeymike_W]

The code for the error seems to be working its way down the range of cells, so if the error is in Q12 the box will appear another nine times

 

[sheetspread]

Have you tried post #6 (it was just a minute before you wrote again so probably not)? If it's not working still show me a sample, or how to fix mine:

Excel 2010
	G
5	0.41033
6	0.34616
7	0.55644
8	0.571979
9	0.642927
10	0.840392
11	0.343535
12	0.337543
13	0.537315
14	0.920642
15	0.147249
16	0.344085
17	0.867084
18	0.548094
19	0.251324
20	0.48565
Sheet1

 

[Mikeymike_W]

Hi,

Well the message box is only appearing once now so that's cool but it appears whenever any value is entered in the cell.

I don't want the user to enter a range of values (17 - 25.5) rather just the one number (19.5). if the enter anything other than the one number I was wanting the message box to appear and tell them to enter the data as I want.

I had this before with data validation but if it can be done as part of this code I think it would be tidier.

Thanks for your support,

Mike

 

[mikerickson]

Sub mySub()
Dim ErrorFlag as Boolean

ErrorFlag = False

For i = 5 To 20
     With Sheets(1).Cells(i, 7)
        If IsError(.Value) Then
            ErrorFlag = True
        ElseIf IsNumeric(.Value) Then
            .Value = Application.MRound(.Value, 0.25)
        End If
    End With
Next i

If ErrorFlag Then MsgBox "There was an error somewhere"

End Sub