Can you advise an edit for this code in use please as i currently see #VALUE

[ipbr21054]

Morning,

My sheet is like so,
Column C Sale Price
Column D P & P
Column E Total Sale Price


The formula on the sheet in Column D is like so & continues down the page.

=IF(AND(LEN(B6)=14,(MID(B6,3,1)&MID(B6,9,1))="--"),0,"")

I enter a value in cell C
When i leave the cell straight away in cell E i i then see #VALUE
This #VALUE text is gone when i then enter a value in cell D

Column E formula is like so & continues down the page.

=IF(C6="","",C6+D6)

It would be nice NOT to see the #VALUE every time

 

[jmacleary]

Hi there.

Change your formula in E6 like so:

=IF(C6="","",IF(ISERROR(C6+D6),"",C6+D6))

But it may be better to do something like this so that you can still see something needs doing:

=IF(C6="","",IF(ISERROR(C6+D6),"...",C6+D6))

This will put three dots in cell E until D has a value.

 

[RoryA]

You could just use =SUM(C6:D6) and return 0 as a default.

 

[ipbr21054]

Hi,
I delete the current code in cell E & replace it with any mentioned above.
I press enter to leave the cell but i then click the same cell to see what i just placed there gone & it revrts back to what i just deleted ???

 

[jmacleary]

Do you have any macros that might be changing things?

 

[ipbr21054]

Hi,

I would like to use your code.

=IF(C6="","",IF(ISERROR(C6+D6),"",C6+D6))

Just seen this on the page code.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("E4:E30")) Is Nothing Then
    Application.EnableEvents = False
    Range("E4:E30").Formula = "=IF(C4="""","""",C4+D4)"
    Application.EnableEvents = True
  End If
  
'   Exit if more than one cell updated at a time
    If Target.Count > 1 Then Exit Sub

'   Check to see if value updated is in column B or D
    If Target.Column = 2 Or Target.Column = 3 Then
        Application.EnableEvents = False
        If UCase(Cells(Target.Row, "B")) = "REFUND" Then
            Cells(Target.Row, "C") = Abs(Cells(Target.Row, "C")) * -1
        Else
            If Cells(Target.Row, "B") = "" Then Cells(Target.Row, "C").ClearContents
        End If
        Application.EnableEvents = True
    End If
    If Not (Application.Intersect(Target, Range("A3:G28")) _
      Is Nothing) Then
        With Target
            If Not .HasFormula Then
                Application.EnableEvents = False
                .Value = UCase(.Value)
                Application.EnableEvents = True
            End If
        End With
    End If

End Sub

Is it just a matter of replacing the code in red with yours or does the code also need to be changed ?

Thanks

 

[jmacleary]

Yes. You would need it to read:

   Range("E4:E30").Formula = "IF(C4="""","""",IF(ISERROR(C4+D4),"""",C4+D4))"

 

[ipbr21054]

Yes. You would need it to read:

   Range("E4:E30").Formula = "IF(C4="""","""",IF(ISERROR(C4+D4),"""",C4+D4))"

Thanks all done now