IF/Nested IF Statement help!

L

Legacy 313594

Guest
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
Code: 
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?

Code: 
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
What exactly are you trying to accomplish?:

If these are all the same condition:
Code: 
[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:
Code: 
[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.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Add Checkbox with new table row
Replies
2
Views
137
vanwooten
V
N
  • Question
Formula help to search a table for a date greater than today that has a non-blank cell and return the date in a separate cell.
Replies
2
Views
155
Alex Blakenburg
A
R
  • Solved
Vlookup help for comma separated values
2
Replies
13
Views
269
Peter_SSs
Peter_SSs
C
  • Solved
VBA Capturing last text character in a cells calculation
Replies
1
Views
82
cogswel__cogs
C
R
  • Question
Extracting data on the basis of multiple conditions
2
Replies
19
Views
1K
OaklandJim
O

Forum statistics

Threads
1,214,812
Messages
6,121,699
Members
449,048
Latest member
81jamesacct

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top