How to add cell rounding into an existing VBA module

[mlepesant]

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.

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

'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:

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

 

[rlv01]

Try this

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

 

[mlepesant]

Try this

    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.