Intermitant RTE 1004

[ipbr21054]

Afternoon,
I am having some issues with a RTE as shown in screen shot supplied.

This is how my worksheet code should operate.

INCOME 1
Values are in cells C30 D30 E30 F30

I then run this code below which should copy the values mentioned above, This is done when the worksheet is full.
The code will then place the values as advised into this worksheet / cells as shown below.

INCOME 2
C4 D4 E4 F4

Private Sub CommandButton1_Click()
  Sheets("INCOME (2)").Range("C4:D4").Value = Sheets("INCOME (1)").Range("C30:E30").Value
  Sheets("INCOME (2)").Range("E4").Value = Sheets("INCOME (1)").Range("E30").Value
  Sheets("INCOME (2)").Range("F4").Value = Sheets("INCOME (1)").Range("F30").Value
  Sheets("INCOME (2)").Activate
  ActiveSheet.Range("A5").Select
  If Sheets("INCOME (2)").Range("G32").Value <> Sheets("INCOME (1)").Range("G32").Value Then MsgBox "Balance of sheets incorrect", vbCritical, "G32 CELLS DO NOT MATCH"
  End Sub

When i use DEBUG i see this shown in yellow.

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(C5="","",IF(ISERROR(C5+D5),"",C5+D5))"
    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

I now see the RTE as mentioned BUT doesnt happen each time ?

Can you see why.
Thanks

 

[JB2020]

You need to double up the quotation marks within a string of text

"=IF(C5="""","""",IF(ISERROR(C5+D5),"""",C5+D5))"

Otherwise it will just see it as a collection of individual strings

"=IF(C5="
","
",IF(ISERROR(C5+D5),"
",C5+D5))"

 

[ipbr21054]

Hi,
I have made that change and the RTE did not show BUT what i have noticed is then the value that is copied from cell E30 ISNT placed in cell E4

Please see screen shot

 

[JB2020]

Hi,
I have made that change and the RTE did not show BUT what i have noticed is then the value that is copied from cell E30 ISNT placed in cell E4

Please see screen shot

You may need to qualify your ranges with the worksheet, though It's hard to tell from just seeing the code.

 

[ipbr21054]

I see an issue.
The cell is empty but after running the code i then see a formula entered into that cell

 

[ipbr21054]

This is information for the worksheet where the values should be pasted.
This is just for column E

Cell E4 empty & no formula
Then starting at Cell E5 this code is used & goes down to Cell E28

=IF(C5="","",IF(ISERROR(C5+D5),"",C5+D5))

When i run the code to copy / paste values the above changes,see below after code was run.

Cell E4 is empty BUT has the IF formula BUT it starts IF(C5 etc etc
This code goes down to cell E29
Cell E29 also has the correct value but this value is hidden from the correct cell of which is E4 because of the formula

 

[ipbr21054]

OK some progress.

The cells that will have the values copied from them have a =SUM formula in them all "these seem to be ok" apart from Cell E30 which has an IF formula.

As a test which give me the wrong answer i did this.
Change the cell for the copy from E30 to D30 but still the formula is entered in the pasted cell ?

 

[ipbr21054]

Forgetting the use of the transfer code ie copy / paste etc i did this.

Clicked on the cell E4, it has no value & there is no formula.
So in cell E4 i put this =(D20) and when i then left the cell and clicked back on it this appears.

=IF(C5="","",IF(ISERROR(C5+D5),"",C5+D5))