How to add cell rounding into an existing VBA module

mlepesant

New Member
Joined
Jun 16, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am using the following code to transform a set of data. I would like to add the rounding of all the numbers to zero decimals in ranges "E6:E", and "I6:AE" below.

I uploaded a picture of how the numbers currently look.

*************

VBA Code:
'MAX-SHORT conditional formatting
    With Range("E6:E" & num_rMat).FormatConditions.Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="0")
        .Interior.Color = RGB(230, 184, 183)
    End With
    'Data conditional formatting
    With Range("I6:AE" & num_rMat).FormatConditions.Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="0")
        .Interior.Color = RGB(255, 128, 128)
    End With

*************

Below is the entire code (the 2 above are at the end of the module) if that helps:
VBA Code:
Sub Process_All()
'
    Dim num_rAlma, del_row As Long
   
    num_rAlma = Sheets("Alma").UsedRange.Rows.Count
   
        '--UPDATE ALMA TAB--'
   
    'Names of "yellow" columns
    Range("AV1").Value = "XX"
    Range("AW1").Value = "Ref"
    Range("AX1").Value = "Code"
    'Generate and copy formulas
    Range("AW4").Formula = "=A4&B4"
    Range("AX4").Formula = "=LEFT(AW4,1)"
    Range("AW4:AX4").Copy
    Range("AW4:AX" & num_rAlma).PasteSpecial xlPasteFormulas
    Range("AV1:AX" & num_rAlma).Interior.ColorIndex = 6 'Fill cells yellow
    'Replace comma with point
    Range("C4:AU" & num_rAlma).Replace What:=",", Replacement:=".", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("A1").Select
    Sheets("Alma").Calculate
   
    '--UPDATE Mat - 20W TAB--'
    Sheets("Mat - 20W").Select
    'Reset values
    num_rMat = Sheets("Mat - 20W").UsedRange.Rows.Count
    num_cMat = Sheets("Mat - 20W").UsedRange.Columns.Count
    Range("D6").Resize(num_rMat, num_cMat).Clear
    Range("D6").Resize(num_rMat, num_cMat).ClearFormats
    'Update Pivot Table
    With ActiveSheet.PivotTables("PivotTable2")
        .PivotCache.SourceData = "Alma!" & Sheets("Alma").Range("A1:AX" & num_rAlma).Address(False, False, xlA1, True)
        .PivotCache.Refresh
        .PivotFields("Code").PivotItems("(blank)").Visible = False
    End With
    'Get new number of rows
    With ActiveSheet.PivotTables("PivotTable2").TableRange2
        num_rMat = .Rows.Count + 3
    End With
    'Populate formulas
    Range("D6").Formula = "=IFERROR(LEFT(B6,FIND(""-"",B6,1)-7),"""")"
    Range("E6").Formula = "=IF($B6="""","""",MIN(I6:AE6))"
    Range("I6").Formula = "=IFERROR(IF($B6="""","""",INDEX(Alma!$A$1:$AX$" & _
        num_rAlma & ",MATCH($B6&""Stock Projeté"",Alma!$AW:$AW,0),MATCH(I$5,Alma!$A$3:$AU$3,0))),0)"
    Range("AF6").Formula = "=IF($B6="""","""",IF(COUNTIF(I6:AE6,""<0"")>0,""S"",""B""))"
    Range("AG6").Formula = "=IF($B6="""","""",INDEX(Alma!$A$1:$AX$" & _
        num_rAlma & ",MATCH($B6&""Stock Sécu"",Alma!$AW:$AW,0),MATCH(I$5,Alma!$A$3:$AU$3,0)))"
    'Copy formulas
    Range("D6:E6").Copy
    Range("D6:E" & num_rMat).PasteSpecial xlPasteFormulas
    Range("I6").Copy
    Range("I6:AE" & num_rMat).PasteSpecial xlPasteFormulas
    Range("AF6").Copy
    Range("AF6:AF" & num_rMat).PasteSpecial xlPasteFormulas
    Range("AG6").Copy
    Range("AG6:BC" & num_rMat).PasteSpecial xlPasteFormulas
    'Calculate
    Sheets("Mat - 20W").Calculate
   
    '--FORMAT Mat - 20W TAB--'
    'Draw borders
    Range("A6:BC" & num_rMat).Select
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    'Center data
    Range("D6:BC" & num_rMat).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
    'MAX-SHORT conditional formatting
    With Range("E6:E" & num_rMat).FormatConditions.Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="0")
        .Interior.Color = RGB(230, 184, 183)
    End With
    'Data conditional formatting
    With Range("I6:AE" & num_rMat).FormatConditions.Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="0")
        .Interior.Color = RGB(255, 128, 128)
    End With
    With Range("I6:AE" & num_rMat).FormatConditions.Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="=AG6")
        .Interior.Color = RGB(255, 204, 0)
    End With
   
    ActiveSheet.PageSetup.PrintArea = "$A$1:$AE$" & num_rMat
    Range("A1").Select

   
End Sub
 
Last edited by a moderator:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,139
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Try this

VBA Code:
    Range("E6:E" & num_rMat).NumberFormat = "0"
    Range("I6:AE" & num_rMat).NumberFormat = "0"
 
Solution

mlepesant

New Member
Joined
Jun 16, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Try this

VBA Code:
    Range("E6:E" & num_rMat).NumberFormat = "0"
    Range("I6:AE" & num_rMat).NumberFormat = "0"
Thank you so much. It does what I needed. I will mark as resolved.
 

Forum statistics

Threads
1,147,477
Messages
5,741,356
Members
423,657
Latest member
Medrok2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top