rounding a whole column

[Krokolv]

Hi,

I tried to round a all column into 2 decimals, but I keep getting the same mistake. So this is the code on the internet:

Sub rounding()
For Each cell In [X:X]
If cell = "" Then Exit Sub
cell.Value = WorksheetFunction.Round(cell.Value, 2)
Next cell
End Sub

But I keep getting the same error

Type mismatch (Error 13)​

What I want to do is actually select the from X2 to "where is values" down and then rounding to 2 decimals, copying and paste on the W column. But if you would guide at least why there is this error, eventually I would figure the rest of the steps out.

Thanks for helping!

 

[JGordon11]

Since the Round function takes a numeric data type there is likely something in column X that is non-numeric giving you a type mismatch error.

If you only care about how the number is displayed you can use

Sub rounding()
    Range("X2:X" & [X1048576].End(xlUp).Row).NumberFormat = "0.00"
End Sub

If you do actually want ot overwrite the original value or fomula with a two decimal version of itself use:

Sub Rounding1()
    Dim X As Range, i As Long, rng, r
    Set X = Range("X2:X" & [X1048576].End(xlUp).Row)
    rng = X
    For Each r In rng
        i = i + 1
        If IsNumeric(r) Then rng(i, 1) = Round(r, 2)
    Next r
    With X
        .Value = rng
        .NumberFormat = "0.00"
    End With
End Sub

 

[Fluff]

How about

Sub Krokolv()
   With Range("X2", Range("X" & Rows.Count).End(xlUp))
      .Offset(, -1).Value = Evaluate(Replace("if(@="""","""",if(isnumber(@),round(@,2),@))", "@", .Address))
   End With
End Sub

This will put the rounded values into col W

 

[Krokolv]

Hi,

both

How about

Sub Krokolv()
   With Range("X2", Range("X" & Rows.Count).End(xlUp))
      .Offset(, -1).Value = Evaluate(Replace("if(@="""","""",if(isnumber(@),round(@,2),@))", "@", .Address))
   End With
End Sub

This will put the rounded values into col W

Worked perfectly, is it too much to ask some comments? I am finding trouble to understand what is @.

 

[Krokolv]

Since the Round function takes a numeric data type there is likely something in column X that is non-numeric giving you a type mismatch error.

If you only care about how the number is displayed you can use

Sub rounding()
    Range("X2:X" & [X1048576].End(xlUp).Row).NumberFormat = "0.00"
End Sub

If you do actually want ot overwrite the original value or fomula with a two decimal version of itself use:

Sub Rounding1()
    Dim X As Range, i As Long, rng, r
    Set X = Range("X2:X" & [X1048576].End(xlUp).Row)
    rng = X
    For Each r In rng
        i = i + 1
        If IsNumeric(r) Then rng(i, 1) = Round(r, 2)
    Next r
    With X
        .Value = rng
        .NumberFormat = "0.00"
    End With
End Sub

Worked perfectly, thanks a lot, would you mind to add some comments to your code?

 

[Fluff]

The Replace function replaces the @ with .Address so that you get something like

      .Offset(, -1).Value = Evaluate("if(X2:X100="""","""",if(isnumber(X2:X100),round(X2:X100,2),X2:X100))")

and the evaluate function then calculates that formula & puts the result in column W.
HTH