Stop Formula in a Column from filling with zeros on load

Stop Formula in a Column from filling with zeros on load

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

• 

  tthomps

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  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

• 

  Ruchir.Agarwal

  

  • Post Options:
  • Link
    Copy

  Posted 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))”

• 

  tthomps

  • Post Options:
  • Link
    Copy

  Posted 27 October 2020, 5:10 am EST

  Thank you so much!