Recover the displayed result of a formula

Question

I try to retrieve the value displayed in cell:

Private Sub Check_Commentaires()

    'Declarations des variables
    Dim WB1 As Workbook
    Dim i, val2
    Dim val1 As Variant
    Set WB1 = ActiveWorkbook
    
    For i = 2 To 10000
        val1 = WB1.Sheets("Avant").Cells(i, 7).Value
        val2 = WB1.Sheets("Avant").Cells(i, 14).Value
        If (val1 = "#N/A" And val2 = "") Or (val1 = "0" And val2 = "") Then
            ThisWorkbook.Sheets("Feuil1").Cells(4, 6).Value = "NOK"
            WB1.Close False
            Exit Sub
        Else
            ThisWorkbook.Sheets("Feuil1").Cells(4, 6).Value = "OK"
        End If
    Next
        WB1.Close False
End Sub

but in this cell (i,7) I have =recherchev(something...) so it doesn't work with error message "type compatibility".

Any idea to store the result displayed in WB1.Sheets("Avant").Cells(i, 7) as a string?

Answer 1

Please, try replacing of:

       If (val1 = "#N/A" And val2 = "") Or (val1 = "0" And val2 = "") Then
            ThisWorkbook.Sheets("Feuil1").Cells(4, 6).Value = "NOK"
            WB1.Close False
            Exit Sub
        Else
            ThisWorkbook.Sheets("Feuil1").Cells(4, 6).Value = "OK"
        End If

with

  If IsError(val1) Then ' error
        If val1 = CVErr(2042) And val2 = "" Then 'N#A error and empty string in val2
           ThisWorkbook.Sheets("Feuil1").cells(4, 6).Value = "NOK"
           wb1.Close False
           Exit Sub
        End If
  ElseIf val1 = "0" And val2 = "" Then                                'no error
        ThisWorkbook.Sheets("Feuil1").cells(4, 6).Value = "NOK"
        wb1.Close False
        Exit Sub
  Else
      ThisWorkbook.Sheets("Feuil1").cells(4, 6).Value = "OK"
  End If

val2 = "0" should mean that it is a 0 string. Is this your case?