Recover the displayed result of a formula-CodePudding

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?

CodePudding user response：

If I am correct, recherchev is the dutch version of VLookup. This returns #N/A when nothing is found, but be aware that this is not the string "#N/A", it's an error which is an own data type in VBA.

You can't compare an error with a string, you'll get a type mismatch.

As BigBen wrote in the comments, you can use WorksheetFunction.IsNA(val1) to check if it contains an error. However, if you change you If-statement to:

If (WorksheetFunction.IsNA(val1) And val2 = "") Or (val1 = "0" And val2 = "") Then

you will still get the same runtime error in case val1 holds #N/A because of the second condition - val1 = "0" still throws that error. VBA will evaluate all parts of an If-statement, even if the first part is true (and therefore the whole If-condition is as TRUE Or anything will always be true).

Easiest solution for your case:

val1 = WB1.Sheets("Avant").Cells(i, 7).Value
If WorksheetFunction.IsNA(val1) then val1 = 0
val2 = WB1.Sheets("Avant").Cells(i, 14).Value
If val1 = 0 And val2 = "" Then
   (...)
Else
   (...)
End If

CodePudding user response：

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?