Help creating formula in VBA

[HansV]

Thanks for the useful addition!

[Asher]

Could you post a stripped down copy of the workbook that demonstrates the problem but that doesn't contain any sensitive data? It has become too difficult to understand what's going on.

Ok, here it is.

[HansV]

If I understand your workbook correctly, you don't really need the formulas in D49:O49 to be replaced with values, but those in D52:O53, for that's the source range of the chart. Or am I wrong?

[Asher]

If I understand your workbook correctly, you don't really need the formulas in D49:O49 to be replaced with values, but those in D52:O53, for that's the source range of the chart. Or am I wrong?

D52:O53 is teh source of range for the chart, but D49:O49 is a source for another Workbook that keeps reading the cells as filled but not 0 because of the formula.

[HansV]

Since the cells that can be changed by the user are on sheet Sheet2, I'd use the Worksheet_Change event of that sheet. Here is a start, you can expand it to replace more formulas with their values. Take care to do it in the correct order.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Range("N1,P4:AA302"), Target) Is Nothing Then
    With Worksheets("Sheet1")
      .Unprotect "password"
      With .Range("D49:O49")
        .Formula = "=IFERROR(IF('Sheet2'!$N$1>=D$47,'Sheet2'!AF303,""""),"""")"
        .Value = .Value
      End With
      With .Range("D52:O52")
        .Formula = "=IFERROR(IF('Sheet2'!$N$1>=D$47,D49/D48,""""),"""")"
        .Value = .Value
      End With
      With .Range("D53:O53")
        .Formula = "=IFERROR(IF('Sheet2'!$N$1>=D$47,D49/D50,""""),"""")"
        .Value = .Value
      End With
      .Protect "password"
    End With
  End If
End Sub

If you change a cell in the range P4AA302 on Sheet2, then switch to Sheet1, you'll see the result.