Stop Formula in a Column from filling with zeros on load

Posted by: tthomps on 22 October 2020, 2:28 am EST

  • Posted 22 October 2020, 2:28 am EST

    Hi folks, I have two different forms that results based on cell formulas are calculated. The disign code is essentially identical. For the first form column 3 is filled with zeros on the load, and for the second form column 4 is not filled with zeros on load. I would like the column cells to be blank until the user enters data into cells to the left. I have tried all machinations of AutoCalculation true and false and Suspend/ResumeFormulaParsing. What am I missing

    First Form:
    With .Sheets(1I)
    .SheetName = "Dry Data"
    .ColumnCount = 8I
    .RowCount = 500I
    .AutoCalculation = True
    .ColumnHeader.Rows(0I).Height = 40.0F
    .ColumnHeader.Columns(0I).Label = "Sample ID"
    .ColumnHeader.Columns(1I).Label = "Thickness 1"
    .ColumnHeader.Columns(2I).Label = "Thickness 2"
    .ColumnHeader.Columns(3I).Label = "Area"
    .ColumnHeader.Columns(4I).Label = "Max Load"
    .ColumnHeader.Columns(5I).Label = "Compressive Strength"
    .ColumnHeader.Columns(6I).Label = "Notes"
    .ColumnHeader.Columns(7I).Label = "Use in Report"
    .Columns(0I).CellType = ctShortText
    .Columns(0).Width = 100I
    .Columns(1I).CellType = ctNumTwoDecimals
    .Columns(2I).CellType = ctNumTwoDecimals
    .Columns(3I).CellType = ctNumTwoDecimals
    .Columns(3I).Formula = "PRODUCT(B1,C1)"
    .Columns(4I).CellType = ctNumTwoDecimals
    .Columns(5I).CellType = ctNumTwoDecimals
    .Columns(5I).Formula = "(E1 / D1)"
    .Columns(5I).Width = 80.0F
    .Columns(6I).CellType = ctLongTextWithWordWrap
    .Columns(6I).Width = 300.0F
    .Columns(7I).CellType = ctCheckbox
    .Columns(7I).HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Center
    End With

    Second form:
    With .Sheets(1I)
    .SheetName = "Data"
    .ColumnCount = 8I
    .RowCount = 500I
    .AutoCalculation = True
    .ColumnHeader.Rows(0I).Height = 40.0F
    .ColumnHeader.Columns(0I).Label = "Sample ID"
    .ColumnHeader.Columns(1I).Label = "Initial Dry Wt."
    .ColumnHeader.Columns(2I).Label = "Final Dry Wt."
    .ColumnHeader.Columns(3I).Label = "Spec. Grav. (C97)"
    .ColumnHeader.Columns(4I).Label = "Average Wt."
    .ColumnHeader.Columns(5I).Label = "Abrasion Resistance"
    .ColumnHeader.Columns(6I).Label = "Notes"
    .ColumnHeader.Columns(7I).Label = "Use in Report"
    .Columns(0I).CellType = ctShortText
    .Columns(0).Width = 100I
    .Columns(1I).CellType = ctNumTwoDecimals
    .Columns(2I).CellType = ctNumTwoDecimals
    .Columns(3I).CellType = ctNumTwoDecimals
    .Columns(3I).Width = 70.0F
    .Columns(4I).CellType = ctNumTwoDecimals
    .Columns(4I).Formula = "AVERAGE(B1:C1)"
    .Columns(5I).CellType = ctNumTwoDecimals
    .Columns(5I).Width = 80.0F
    .Columns(5I).Formula = "(10.95 * D1) * ((2000 + E1) / (2000 * (B1 - C1)))"
    .Columns(6I).CellType = ctLongTextWithWordWrap
    .Columns(6I).Width = 300.0F
    .Columns(7I).CellType = ctCheckbox
    .Columns(7I).HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Center
    End With

    Best regards, Todd
  • Marked as Answer

    Replied 23 October 2020, 4:26 am EST

    Hello Todd,

    To keep the formula cells blank, you can set the formula like:
    .Columns(3I).Formula = "IF(PRODUCT(B1,C1)=0,"""",PRODUCT(B1,C1))"
    Regards,
  • Replied 27 October 2020, 5:10 am EST

    Thank you so much!
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels