Currently have a macro with a lot of If statments which goes through each row and tests for certain conditions. One of the conditions which needs to be added to every if statement is the following: If c1 (which I have Dim'd as a range) = "#N/A"). After asking a few people, I finally got the correct line of code to add before each of my if statements as another condition it is:

     If CVErr(cl.Value) = CVErr(xlErrNA) And

Unfortunately this keeps messing up when the cl.Value was something NOT "#N/A". I wanted to see if there was a way to maybe start off the macro such as Do While or something that starts it off by testing to see if cl.Value is "#N/A" and if it is go through all the other IF statements and if not go to the next cell. Please advise. Thanks for your help.

Here is just a quick example/peek at 4 If statements which I have added the above line which tests for "#N/A" which brings up the error (Says 13: type mismatch).

  For Each cl In range("A2", range("A" & Rows.count).End(xlUp))
  

     If CVErr(cl.Value) = CVErr(xlErrNA) And _
     cl.Offset(, 11).Value = "Bond Deals" And cl.Offset(, 2).Value Like "*CLO*" Then
            cl.Value = "CLO"
     End If
     
     If CVErr(cl.Value) = CVErr(xlErrNA) And cl.Offset(, 11).Value = "Bond Deals" And cl.Offset(, 12).Value Like "BCC*" Then
            c1.Value = "CLO"
     End If
     
     If CVErr(cl.Value) = CVErr(xlErrNA) And _
     cl.Offset(, 11).Value = "Bond Deals" And cl.Offset(, 14).Value Like "*CLO*" Then
            c1.Value = "CLO"
     End If

     If CVErr(cl.Value) = CVErr(xlErrNA) And _
     cl.Offset(, 11).Value = "Bond Deals" And cl.Offset(, 15).Value Like "*CLO*" Then
            c1.Value = "CLO"
     End If

From Excel Select Case help...    
"This example displays a message if the active cell contains a cell error value.
You can use this example as a framework for a cell-error-value error handler."
'---   
If IsError(ActiveCell.Value) Then    
  errval = ActiveCell.Value    
  Select Case errval    
  Case CVErr(xlErrDiv0)    
    MsgBox "#DIV/0! error"
  Case CVErr(xlErrNA)    
    MsgBox "#N/A error"
  Case CVErr(xlErrName)    
    MsgBox "#NAME? error"
  Case CVErr(xlErrNull)    
    MsgBox "#NULL! error"
  Case CVErr(xlErrNum)    
    MsgBox "#NUM! error"
  Case CVErr(xlErrRef)    
    MsgBox "#REF! error"
  Case CVErr(xlErrValue)    
    MsgBox "#VALUE! error"
  Case Else    
    MsgBox "This should never happen!!"
  End Select    
End If
'---
Jim Cone
Portland, Oregon USA
XL Companion Excel add-in compares, counts removes, finds and
https://jumpshare.com/b/O5FC6LaBQ6U3UPXjOmX2

From Excel Select Case help...    
"This example displays a message if the active cell contains a cell error value.
You can use this example as a framework for a cell-error-value error handler."
'---   
If IsError(ActiveCell.Value) Then    
  errval = ActiveCell.Value    
  Select Case errval    
  Case CVErr(xlErrDiv0)    
    MsgBox "#DIV/0! error"
  Case CVErr(xlErrNA)    
    MsgBox "#N/A error"
  Case CVErr(xlErrName)    
    MsgBox "#NAME? error"
  Case CVErr(xlErrNull)    
    MsgBox "#NULL! error"
  Case CVErr(xlErrNum)    
    MsgBox "#NUM! error"
  Case CVErr(xlErrRef)    
    MsgBox "#REF! error"
  Case CVErr(xlErrValue)    
    MsgBox "#VALUE! error"
  Case Else    
    MsgBox "This should never happen!!"
  End Select    
End If
'---
Jim Cone
Portland, Oregon USA
XL Companion Excel add-in compares, counts removes, finds and
https://jumpshare.com/b/O5FC6LaBQ6U3UPXjOmX2

I'm confused? I'm no longer using Select Case...I'm using IF statements. Is there a way I can use Select Case instead of a Do While loop? Wouldn't it be smart to have a nested if loop? Like start it off with something
like Do While c1 (or whatever A2/when it's next it'll be A3/etc etc)
CVErr(cl.Value) = CVErr(xlErrNA) so it'll only go through all those IF
statements if it meets that one condition. And I just checked on average how
many are #N/A and it's around 400 (which is alot better than 1000). Please
advise if you think this'll work and how I should go about this.

The select case statement only executes if there is an error in the cell.
See the first line of the code:   "If IsError(ActiveCell.Value) Then"
Which you would change to:     If IsError(cl.Value) Then   

Also, the MsgBox statements are only in there for illustrative purposes only.
You would enter code in place of the MsgBox to take whatever action desired.

I suspect you only need...
  If IsError(cl.Value) Then
     'check for bond deals and CLO
  End If
'---
Jim Cone

The select case statement only executes if there is an error in the cell.
See the first line of the code:   "If IsError(ActiveCell.Value) Then"
Which you would change to:     If IsError(cl.Value) Then   

Also, the MsgBox statements are only in there for illustrative purposes only.
You would enter code in place of the MsgBox to take whatever action desired.

I suspect you only need...
  If IsError(cl.Value) Then
     'check for bond deals and CLO
  End If
'---
Jim Cone

Okay, I see what you are saying. Then in theory would this work? All I need to do is put If IsError(cl.Value) Then right after the first line of code (For Each) and then if that is true it'll go through every other line and if it is false, it'll skip till the end of that End If and start on the next row?? Correct? I'm sorry i'm just confused how Nested If Loops work.

For Each cl In range("A2", range("A" & Rows.count).End(xlUp)) 

If cl.Offset(, 11).Value = "Bond Deals" And cl.Offset(, 1).Value Like "*MBS*" Then
            cl.Value = "MBS"
     End If
     
     If cl.Offset(, 11).Value = "Bond Deals" And (Len(cl.Offset(, 12).Value) = 9 Or Len(cl.Offset(, 17).Value) = 12) Then
           cl.Value = "Bond"
     End If
     
     If cl.Offset(, 11).Value = "Bond Deals" And (Len(cl.Offset(, 13).Value) = 9 Or Len(cl.Offset(, 18).Value) = 12) Then
           cl.Value = "Bond"
     End If

Can anyone help?

This is what I think you are after...
Sub Example()

For Each cl In Range("A2", Range("A" & Rows.Count).End(xlUp)).Cells
   If Not IsError(cl.Value) Then 'no error
       If cl.Offset(, 11).Value = "Bond Deals" And _
          cl.Offset(, 1).Value Like "*MBS*" Then
          cl.Value = "MBS"
       End If

       If cl.Offset(, 11).Value = "Bond Deals" And _
          (Len(cl.Offset(, 12).Value) = 9 Or Len(cl.Offset(, 17).Value) = 12) Then
          cl.Value = "Bond"
       End If

      If cl.Offset(, 11).Value = "Bond Deals" And _
         (Len(cl.Offset(, 13).Value) = 9 Or Len(cl.Offset(, 18).Value) = 12) Then
          cl.Value = "Bond"
      End If
   Else
        'have error in cell - can do nothing and loop skips to next cell or ?
   End If
Next 'cl

End Sub

           

This is what I think you are after...
Sub Example()

For Each cl In Range("A2", Range("A" & Rows.Count).End(xlUp)).Cells
   If Not IsError(cl.Value) Then 'no error
       If cl.Offset(, 11).Value = "Bond Deals" And _
          cl.Offset(, 1).Value Like "*MBS*" Then
          cl.Value = "MBS"
       End If

       If cl.Offset(, 11).Value = "Bond Deals" And _
          (Len(cl.Offset(, 12).Value) = 9 Or Len(cl.Offset(, 17).Value) = 12) Then
          cl.Value = "Bond"
       End If

      If cl.Offset(, 11).Value = "Bond Deals" And _
         (Len(cl.Offset(, 13).Value) = 9 Or Len(cl.Offset(, 18).Value) = 12) Then
          cl.Value = "Bond"
      End If
   Else
        'have error in cell - can do nothing and loop skips to next cell or ?
   End If
Next 'cl

End Sub

           

This seems like what I want to do (except I "want" it to be error). This is what I have and it comes up as an error because it says "Compile error: Next without For" even though I stil have the For Each at the beginning. Check it out.

For Each cl In range("A2", range("A" & Rows.count).End(xlUp))
  
If IsError(cl.Value) Then
 If cl.Offset(, 11).Value = "Cash" And cl.Offset(, 2).Value Like "*CLO*" Then
            cl.Value = "CLO"
     End If
     
     If cl.Offset(, 11).Value = "Cash" And cl.Offset(, 14).Value Like "CLO*" Then
            cl.Value = "CLO"
     End If
     
     If cl.Offset(, 11).Value = "Cash" And cl.Offset(, 15).Value Like "CLO*" Then
            cl.Value = "CLO"
     End If
 If cl.Offset(, 11).Value = "Options" Then
            cl.Value = "Listed Options"
     End If
     
     If cl.Offset(, 11).Value = "Option Deals" Then
            cl.Value = "Listed Options"
     End If
     
Else
     
  Next cl
End If
End Sub

I just changed it to make the End If go before the "Next cl" and it works better now and even goes through. But now while running it, even when all conditions are met when it reaches the line "c1.Value = "CLO"" it says "Run-time error '424': Object required". So.........any recommendations?

Well...
   c1 is not the same as cl

VBA programming can really be a lot fun sometimes. <g>
'---
Jim Cone