Round by 2 decimals

[Romano_odK]

Good morning,

In VBA I have the following sub (see below). I need to round column C by 2 decimals at the end of the sub. Column C is in a table. Is there a way to do this?

Thank you in advance,

Private Sub KopieerNaarVerkoopprijs_Click()
With Sheets("OPXML").ListObjects("Table_Query_from_A100")
.ListColumns(3).DataBodyRange.Value = .ListColumns(4).DataBodyRange.Value
End With
Range("E9:E15000").ClearContents
Range("F9:F15000").ClearContents
Range("G9:G15000").ClearContents
Range("H9:H15000").ClearContents
Range("E7").ClearContents
Range("E7").Value = 0
Range("C9").Select
End Sub

 

[SQUIDD]

Hi

Does the below work

Was there a reason not to clear all the columns in 1 go?

Private Sub KopieerNaarVerkoopprijs_Click()
With Sheets("OPXML").ListObjects("Table_Query_from_A100")
.ListColumns(3).DataBodyRange.Value = .ListColumns(4).DataBodyRange.Value
End With
   Range("E9:H15000").ClearContents
   Range("E7").Value = 0
Columns("C:C").NumberFormat = "0.00"
End Sub

 

[bebo021999]

May be:

Private Sub KopieerNaarVerkoopprijs_Click()
With Sheets("OPXML").ListObjects("Table_Query_from_A100")
.ListColumns(3).DataBodyRange.Value = Evaluate("=ROUND(Table_Query_from_A100[Column4],2)")
End With
Range("E9:H15000").ClearContents
Range("E7").Value = 0
Range("C9").Select
End Sub

 

[Alex Blakenburg]

Just curious, does this mean you are using Power Query ? If so why not do the rounding there ?

With Sheets("OPXML").ListObjects("Table_Query_from_A100")

 

[Romano_odK]

Just curious, does this mean you are using Power Query ? If so why not do the rounding there ?

No I am not, just an Excel sheet with a table.

 

[Romano_odK]

May be:

Private Sub KopieerNaarVerkoopprijs_Click()
With Sheets("OPXML").ListObjects("Table_Query_from_A100")
.ListColumns(3).DataBodyRange.Value = Evaluate("=ROUND(Table_Query_from_A100[Column4],2)")
End With
Range("E9:H15000").ClearContents
Range("E7").Value = 0
Range("C9").Select
End Sub

Good morning,

Tried it but no succes, got a #Value! error in column C.

 

[Romano_odK]

Hi

Does the below work

Was there a reason not to clear all the columns in 1 go?

Private Sub KopieerNaarVerkoopprijs_Click()
With Sheets("OPXML").ListObjects("Table_Query_from_A100")
.ListColumns(3).DataBodyRange.Value = .ListColumns(4).DataBodyRange.Value
End With
   Range("E9:H15000").ClearContents
   Range("E7").Value = 0
Columns("C:C").NumberFormat = "0.00"
End Sub

Tried it but no succes unfortunately.

 

[Alex Blakenburg]

Good morning,

Tried it but no succes, got a #Value! error in column C.

If it works for you to do so, if you replace [Column4] using your real Column Name for the 4th column (enclosed in the square brackets) in the following line:

        .ListColumns(3).DataBodyRange.Value = Evaluate("=ROUND(Table_Query_from_A100[Column4],2)")

I expect that @bebo021999 's solution might work for you.

 

[Romano_odK]

Good morning,

In VBA I have the following sub (see below). I need to round column C by 2 decimals at the end of the sub. Column C is in a table. Is there a way to do this?

Thank you in advance,

Private Sub KopieerNaarVerkoopprijs_Click()
With Sheets("OPXML").ListObjects("Table_Query_from_A100")
.ListColumns(3).DataBodyRange.Value = .ListColumns(4).DataBodyRange.Value
End With
Range("E9:E15000").ClearContents
Range("F9:F15000").ClearContents
Range("G9:G15000").ClearContents
Range("H9:H15000").ClearContents
Range("E7").ClearContents
Range("E7").Value = 0
Range("C9").Select
End Sub

Instead of changing this VBA I could also be hapy if ROUND could be added to this line.. if that works my problem would be solved also.



=IFERROR((IFS(H9>0;K9/(1-H9);G9>0;K9*G9+K9;F9>0;F9;E9>0;E9*AH9+AH9;$D$7>0;$D$7*AH9+AH9;$D$7>0;$D$7*AI9+AI9;$D$7>0;$D$7*AJ9+AJ9));AH9)

 

[Alex Blakenburg]

If the formula always produces a numeric result then just wrap it in a round function.

=ROUND(IFERROR((IFS(H9>0;K9/(1-H9);G9>0;K9*G9+K9;F9>0;F9;E9>0;E9*AH9+AH9;$D$7>0;$D$7*AH9+AH9;$D$7>0;$D$7*AI9+AI9;$D$7>0;$D$7*AJ9+AJ9));AH9),2)