#REF stuffing up my IF Statement

With the help of theses forums i've been able to create what I think is a masterpiece!

But when I run my macro and it returns #REF (which is a result im actually ok with) it throws up an error in my if statement.

So I run XXXXX, this brings across any standing instructions, then i change a value in any cell, say for example I7 i say "Yes" actioned.

the Worksheet_Change sub cracks the sads

I was thinking I might have to include ELSE statements, but im not sure how or if it will even work

Example attached: Corporate Action History Master.xlsm


Below is an extract where it errors

Any thoughts please

Private Sub Worksheet_Change(ByVal Target As Range)

                
       For i = Cells(Rows.Count, "A").End(xlUp).Row To 7 Step -1
      If Cells(i, "I") = "Yes" And Cells(i, "D") = "FIRST NOTIFICATION (AUTO)" And Len(Cells(i, "K") > 5) And Len(Cells(i, "L") > 5) Then
       Sheets("Outstanding").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).EntireRow.Value = Cells(i, 1).EntireRow.Value
        Sheets("Outstanding").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).FormatConditions.Delete
        Cells(i, 1).EntireRow.ClearContents
        MsgBox ("Transfered to outstanding")
        End If
        Next i
        
        For i = Cells(Rows.Count, "A").End(xlUp).Row To 7 Step -1
        If Cells(i, "M") = "Yes" Then
         Sheets("Daily Archive").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).EntireRow.Value = Cells(i, 1).EntireRow.Value
         Sheets("Daily Archive").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).FormatConditions.Delete
         Cells(i, 1).EntireRow.ClearContents
         MsgBox ("moved to archive on exit")
        End If
        Next i
                    
        
End Sub

Hi

Why don't you change the NA() part of the formula to either be a blank ("") or, if you really want to have a non error result, then N/A. I think that would resolve the issue.

rylo

hmm trying both but still failing Error 2023

Rylo i actually used an adaptation of your formula (still using the other one for paydate) to to bring across the comment value

  Range("K7").Formula = "=IF(F7=""BONUS ISSUE-TRADING"", (n/a),IF(F7=""UK"", (n/a),IF(F7=""DEMERGER"", (n/a),IF(F7=""CONSOLIDATION"", (n/a),OFFSET(INDEX('Letters - Bulked'!AC:AC,SUMPRODUCT(--('Letters - Bulked'!$C$2:$C$" & LastRow & "=New!C7),--(LEFT('Letters - Bulked'!$AC$2:$AC$" & LastRow & ",18)=""INSTRUCTIONS RECVD""),ROW('Letters - Bulked'!$J$2:$J$" & LastRow & "))),2,0)))))"
  Range("K7").AutoFill Destination:=Range("K7:K" & Cells(Rows.Count, 1).End(xlUp).Row)
  Range(Range("K7"), Range("K7").End(xlDown)).Value = Range(Range("K7"), Range("K7").End(xlDown)).Value

Hi

Try making the (n/a) into a string in the formula like so ""(n/a)""

rylo

argh that was so simple! thanks rylo