to detect error values in vba

[abhaysanan]

Hi,

i have this code in vba which checks through different cells and if it has an error, it moves to the next cell, or else, it puts some data in a database. The problem I am having is that on my sheet, I see 3 different error values as can be seen in the code. Somehow my code doesnt pick them up. Does any one know if there is an ISNA thing in vba??

 For j = 1 To 23
     
     For i = 1 To 24
     
       If (ActiveSheet.Range("Prices").Cells(i, j).Text <> "#N/A RI Tim") And _
       (ActiveSheet.Range("Prices").Cells(i, j).Text <> "#N/A Trd") And _
       (ActiveSheet.Range("Prices").Cells(i, j).Text <> "#N/A Sec") Then
        
        insBlpRoot = Trim(ActiveSheet.Range("Roots").Cells(i, j).Value)
        insAARoot = Trim(ActiveSheet.Range("AARoot").Cells(i, j).Value)
            
       'inserting into the database
        querystring = "INSERT INTO TradeSymb (" & _
            "BLPRoot, AARoot) " & _
            "VALUES ('" & insBlpRoot & "', '" & insAARoot & "')"
        
        Set qdInsertRoots = Conn.CreateQueryDef("", querystring)
        Set rsRoots = qdInsertRoots.OpenRecordset()
        
    Else
    
    End If
    
    Next i
    Next j

 

[stdz]

Yes you can use IsNA() I think

If IsNA(x) Then
'......
End If

It seems that #N/A is not evaluated as text.

Hope this helps !

Ciao,

Don.

 

[Andrew Poulsom]

Is "#N/A RI Tim" an Excel error, because it's new to me?

You can test for Excel's #N/A error like this:

If ActiveSheet.Range("Prices").Cells(i, j).Value <> CVErr(xlErrNA) Then

 

[abhaysanan]

i dont think its an excel error. its an error i get while tryin to get values from bloomberg!! i'll try out your solutions and c how it works. thanks for your help!

 

[abhaysanan]

HI,

none of the above solutions seem to work. I dont think its an excel error also. And when I put in the code which checks if its an excel error, it showed type mismatch... How do i fix it pls?

 

[Jim North]

Your if statement is using AND as a condition... which means all 3 error message need to exist... change it to OR

 

[abhaysanan]

But i'm making sure the value in my cell is NOT EQUAL to all the three errors...and thats y the usage of AND..I tried using IsNumeric and I think that works..i just check if value is numeric and if it is, i put in in database...

 

[Jim North]

Are the messages you are looking for text in the cells or message generated by Excel. If they are actual text, your compare (IF) will not work. Can you post a sample to the board?

 

[Nimrod]

Instead of going to all the cells in the range and checking for errors why not use a "SpecialCells" loop that ONLY GOES TO CELLS WITH ERRORS ??

The following loop only goes to cells with errors within the designated range...

Public Sub OnlyErrCells()
'ONLY GOTO CELLS THAT HAVE AN ERROR IN RANGE A1:H26
  For Each CellErr In Range("A1:H26").SpecialCells(xlCellTypeFormulas, 16)
  
  MsgBox CellErr.Address
  
  Next CellErr
End Sub