IF/Nested IF Statement help!

[Legacy 313594]

Excel & VBA Folks,

I'm fairly new to VBA, but have been working hard to learn & help my company. I need help consolidating my code though, it's long & continually tests IF statements. The below code runs through about 1500 rows trying to match trade breaks to two seperate spreadsheets depending on whether

cells(i,"H) = "CORPLD or Cells(i,"H")="MUNI"

containing up to 3000 trades, using several IF/ Vlookups. If it returns a match, it moves to the next i (row).

Can anyone help with consolidating my code? Do I have the right idea with the NESTED IF's or is it an overkill?

For i = 2 To N    
    If Cells(i, "H") = "FIX" Or Cells(i, "H") = "LCV" Then
        GoTo Arsenal2:
    End If
    
        If Cells(i, "H") = "MUNI" Then
            GoTo London:
        End If


            If Cells(i, "H") = "CORPLD" Then
                        Cells(i, "U").Formula = "=IFERROR(VLOOKUP(C[-1],'Ladder Trades'!C[-20]:C[-12],9,FALSE),""Non-Trade Related"")"
            End If
            
                If Cells(i, "H") = "CORPLD" And Cells(i, "U") = "Non-Trade Related" Then
                        Cells(i, "U").Formula = "=IFERROR(VLOOKUP(C[-1],'Ladder Trades'!C[-19]:C[-12],8,FALSE),""Non-Trade Related"")"
                End If
                
                    If Cells(i, "H") = "CORPLD" And Cells(i, "U") = "Non-Trade Related" Then
                            Cells(i, "U").Formula = "=IFERROR(VLOOKUP(C[-1],'Ladder Trades'!C[-18]:C[-12],7,FALSE),""Non-Trade Related"")"
                    End If
                    
  If Cells(i, "H") = "CORPLD" And Cells(i, "U") = "Non-Trade Related" Then
     Cells(i, "U").Formula = "=IFERROR(VLOOKUP(C[-1],'Ladder Trades'!C[-17]:C[-12],6,FALSE),""Non-Trade Related"")"
   End If
                        
                            If Cells(i, "H") = "CORPLD" And Cells(i, "U") = "Non-Trade Related" Then
                                    Cells(i, "U").Formula = "=IFERROR(VLOOKUP(C[-1],'Ladder Trades'!C[-16]:C[-12],5,FALSE),""Non-Trade Related"")"
                            End If
                            
                                If Cells(i, "H") = "CORPLD" And Cells(i, "U") = "Non-Trade Related" Then
                                        Cells(i, "U").Formula = "=IFERROR(VLOOKUP(C[-1],'Ladder Trades'!C[-15]:C[-12],4,FALSE),""Non-Trade Related"")"
                                End If
                                
                                    If Cells(i, "H") = "CORPLD" And Cells(i, "U") = "Non-Trade Related" Then
                                            Cells(i, "U").Formula = "=IFERROR(VLOOKUP(C[-1],'Ladder Trades'!C[-14]:C[-12],3,FALSE),""Non-Trade Related"")"
                                    End If
                                    
                                        If Cells(i, "H") = "CORPLD" And Cells(i, "U") = "Non-Trade Related" Then
                                                Cells(i, "U").Formula = "=IFERROR(VLOOKUP(C[-1],'Ladder Trades'!C[-13]:C[-12],2,FALSE),""Non-Trade Related"")"
                                        End If
                    
London:
                            If Cells(i, "H") = "MUNI" Then
                                    Cells(i, "U").Formula = "=IFERROR(VLOOKUP(C[-1],'Muni Trades'!C[-20]:C[-12],9,FALSE),""Non-Trade Related"")"
                            End If
                                
                                    If Cells(i, "H") = "MUNI" And Cells(i, "U") = "Non-Trade Related" Then
                                        Cells(i, "U").Formula = "=IFERROR(VLOOKUP(C[-1],'Muni Trades'!C[-19]:C[-12],8,FALSE),""Non-Trade Related"")"
                                    End If
                                    
                                        If Cells(i, "H") = "MUNI" And Cells(i, "U") = "Non-Trade Related" Then
                                            Cells(i, "U").Formula = "=IFERROR(VLOOKUP(C[-1],'Muni Trades'!C[-18]:C[-12],7,FALSE),""Non-Trade Related"")"
                                        End If
                            
                                            If Cells(i, "H") = "MUNI" And Cells(i, "U") = "Non-Trade Related" Then
                                                    Cells(i, "U").Formula = "=IFERROR(VLOOKUP(C[-1],'Muni Trades'!C[-17]:C[-12],6,FALSE),""Non-Trade Related"")"
                                            End If
                                            
                                                If Cells(i, "H") = "MUNI" And Cells(i, "U") = "Non-Trade Related" Then
                                                        Cells(i, "U").Formula = "=IFERROR(VLOOKUP(C[-1],'Muni Trades'!C[-16]:C[-12],5,FALSE),""Non-Trade Related"")"
                                                End If
                                                    
   If Cells(i, "H") = "MUNI" And Cells(i, "U") = "Non-Trade Related" Then
      Cells(i, "U").Formula = "=IFERROR(VLOOKUP(C[-1],'Muni Trades'!C[-15]:C[-12],4,FALSE),""Non-Trade Related"")"
    End If
                                                    
   If Cells(i, "H") = "MUNI" And Cells(i, "U") = "Non-Trade Related" Then
       Cells(i, "U").Formula = "=IFERROR(VLOOKUP(C[-1],'Muni Trades'!C[-14]:C[-12],3,FALSE),""Non-Trade Related"")"
    End If
                                                    
   If Cells(i, "H") = "MUNI" And Cells(i, "U") = "Non-Trade Related" Then
        Cells(i, "U").Formula = "=IFERROR(VLOOKUP(C[-1],'Muni Trades'!C[-13]:C[-12],2,FALSE),""Non-Trade Related"")"
     End If
                 
Arsenal2:
    Next i

 

[mrmmickle1]

What exactly are you trying to accomplish?:

If these are all the same condition:

[COLOR=#ff0000]If Cells(i, "H") = "MUNI" And Cells(i, "U") = "Non-Trade Related" Then[/COLOR]
    Cells(i, "U").Formula = "=IFERROR(VLOOKUP(C[-1],'Muni Trades'!C[-18]:C[-12],7,FALSE),""Non-Trade Related"")"
End If


[COLOR=#ff0000]If Cells(i, "H") = "MUNI" And Cells(i, "U") = "Non-Trade Related" Then[/COLOR]
    Cells(i, "U").Formula = "=IFERROR(VLOOKUP(C[-1],'Muni Trades'!C[-17]:C[-12],6,FALSE),""Non-Trade Related"")"
End If


[COLOR=#ff0000]If Cells(i, "H") = "MUNI" And Cells(i, "U") = "Non-Trade Related" Then[/COLOR]
    Cells(i, "U").Formula = "=IFERROR(VLOOKUP(C[-1],'Muni Trades'!C[-16]:C[-12],5,FALSE),""Non-Trade Related"")"
End If

You can consolidate like this:

[COLOR=#0000ff]If[/COLOR] Cells(i, "H") = "MUNI" [COLOR=#0000ff]And[/COLOR] Cells(i, "U") = "Non-Trade Related"[COLOR=#0000ff] Then[/COLOR]
    Cells(i, "U").Formula = "=IFERROR(VLOOKUP(C[-1],'Muni Trades'!C[-18]:C[-12],7,FALSE),""Non-Trade Related"")"
    Cells(i, "U").Formula = "=IFERROR(VLOOKUP(C[-1],'Muni Trades'!C[-17]:C[-12],6,FALSE),""Non-Trade Related"")"
    Cells(i, "U").Formula = "=IFERROR(VLOOKUP(C[-1],'Muni Trades'!C[-16]:C[-12],5,FALSE),""Non-Trade Related"")"
[COLOR=#0000ff]End If[/COLOR]

No need to repeat the same condition over and over again Hope this helps! Keep up the good work.