Excel-VBA- This for loop LOOPS too many times with IF/ IfElse conditions

Question

This my code as of now:

Sub copy_paste()

Worksheets(1).Activate
Dim ch As Long
Dim c As Variant
Dim d As Variant
Dim v As Range
Dim w As Range
Dim brow As Long
Dim crow As Long
Dim cc As Variant

Set w = Sheets(1).Range("E:E")
brow = w(w.Cells.Count).End(xlUp).Row
Set w = Range(w(2), w(brow))

Set v = Sheets(1).Range("G:G")
brow = v(v.Cells.Count).End(xlUp).Row
Set v = Range(v(2), v(brow))

            For Each c In v

             For Each d In w
                                d = d.Text
                                bbv1 = " QR "
                                bbv2 = " HCPI "
                If InStr(1, d, "mini") Then

                    c = Mid(c, 1, 5) & " <INDEX>" & bbv2 '& c.Offset(0, 3)
                        'Debug.Print (c)
                ElseIf InStr(1, d, "ix") Then
                        'Debug.Print (c)
                    c = Mid(c, 1, 4) & " <INDEX>" & bbv2
                        'Debug.Print (c)
                Else
                    c = Mid(c, 1, 4) & " <CMDTY>" & bbv2
                        'Debug.Print (c)
                End If
                Debug.Print (c)

                Next d
            Next c

End Sub

Which produces this output:

Edz5 <CMDTY> HCPI 
Edz5 <CMDTY> HCPI 
Edz5  <INDEX> HCPI 
Edz5 <INDEX> HCPI 
Edz5  <INDEX> HCPI 
Edz5 <INDEX> HCPI 
Edz5 <CMDTY> HCPI 
Edz5 <CMDTY> HCPI 
Edz5 <CMDTY> HCPI 
Edz5 <CMDTY> HCPI 
Edz5 <CMDTY> HCPI 
Edz5 <CMDTY> HCPI 
Edz5 <CMDTY> HCPI 
Hcn5 <CMDTY> HCPI 
Hcn5 <CMDTY> HCPI 
Hcn5  <INDEX> HCPI 
Hcn5 <INDEX> HCPI 
Hcn5  <INDEX> HCPI 
Hcn5 <INDEX> HCPI 
Hcn5 <CMDTY> HCPI 
Hcn5 <CMDTY> HCPI 
Hcn5 <CMDTY> HCPI 
Hcn5 <CMDTY> HCPI 
Hcn5 <CMDTY> HCPI 
Hcn5 <CMDTY> HCPI 
Hcn5 <CMDTY> HCPI 
Mesu <CMDTY> HCPI 
Mesu <CMDTY> HCPI 
Mesu  <INDEX> HCPI 
Mesu <INDEX> HCPI 
Mesu  <INDEX> HCPI 
Mesu <INDEX> HCPI 
Mesu <CMDTY> HCPI 
Mesu <CMDTY> HCPI 
Mesu <CMDTY> HCPI 
Mesu <CMDTY> HCPI 
Mesu <CMDTY> HCPI 
Mesu <CMDTY> HCPI 
Mesu <CMDTY> HCPI 
Ptu5 <CMDTY> HCPI 
Ptu5 <CMDTY> HCPI 
Ptu5  <INDEX> HCPI 
Ptu5 <INDEX> HCPI 
Ptu5  <INDEX> HCPI 
Ptu5 <INDEX> HCPI 
Ptu5 <CMDTY> HCPI 
Ptu5 <CMDTY> HCPI 
Ptu5 <CMDTY> HCPI 
Ptu5 <CMDTY> HCPI 
Ptu5 <CMDTY> HCPI 
Ptu5 <CMDTY> HCPI 
Ptu5 <CMDTY> HCPI 
Rtau <CMDTY> HCPI 
Rtau <CMDTY> HCPI 
Rtau  <INDEX> HCPI 
Rtau <INDEX> HCPI 
Rtau  <INDEX> HCPI 
Rtau <INDEX> HCPI 
Rtau <CMDTY> HCPI 
Rtau <CMDTY> HCPI 
Rtau <CMDTY> HCPI 
Rtau <CMDTY> HCPI 
Rtau <CMDTY> HCPI 
Rtau <CMDTY> HCPI 
Rtau <CMDTY> HCPI 
Smu5 <CMDTY> HCPI 
Smu5 <CMDTY> HCPI 
Smu5  <INDEX> HCPI 
Smu5 <INDEX> HCPI 
Smu5  <INDEX> HCPI 
Smu5 <INDEX> HCPI 
Smu5 <CMDTY> HCPI 
Smu5 <CMDTY> HCPI 
Smu5 <CMDTY> HCPI 
Smu5 <CMDTY> HCPI 
Smu5 <CMDTY> HCPI 
Smu5 <CMDTY> HCPI 
Smu5 <CMDTY> HCPI 
Tyu5 <CMDTY> HCPI 
Tyu5 <CMDTY> HCPI 
Tyu5  <INDEX> HCPI 
Tyu5 <INDEX> HCPI 
Tyu5  <INDEX> HCPI 
Tyu5 <INDEX> HCPI 
Tyu5 <CMDTY> HCPI 
Tyu5 <CMDTY> HCPI 
Tyu5 <CMDTY> HCPI 
Tyu5 <CMDTY> HCPI 
Tyu5 <CMDTY> HCPI 
Tyu5 <CMDTY> HCPI 
Tyu5 <CMDTY> HCPI 
Ubu5 <CMDTY> HCPI 
Ubu5 <CMDTY> HCPI 
Ubu5  <INDEX> HCPI 
Ubu5 <INDEX> HCPI 
Ubu5  <INDEX> HCPI 
Ubu5 <INDEX> HCPI 
Ubu5 <CMDTY> HCPI 
Ubu5 <CMDTY> HCPI 
Ubu5 <CMDTY> HCPI 
Ubu5 <CMDTY> HCPI 
Ubu5 <CMDTY> HCPI 
Ubu5 <CMDTY> HCPI 
Ubu5 <CMDTY> HCPI 
Vgu5 <CMDTY> HCPI 
Vgu5 <CMDTY> HCPI 
Vgu5  <INDEX> HCPI 
Vgu5 <INDEX> HCPI 
Vgu5  <INDEX> HCPI 
Vgu5 <INDEX> HCPI 
Vgu5 <CMDTY> HCPI 
Vgu5 <CMDTY> HCPI 
Vgu5 <CMDTY> HCPI 
Vgu5 <CMDTY> HCPI 
Vgu5 <CMDTY> HCPI 
Vgu5 <CMDTY> HCPI 
Vgu5 <CMDTY> HCPI 
Wnu5 <CMDTY> HCPI 
Wnu5 <CMDTY> HCPI 
Wnu5  <INDEX> HCPI 
Wnu5 <INDEX> HCPI 
Wnu5  <INDEX> HCPI 
Wnu5 <INDEX> HCPI 
Wnu5 <CMDTY> HCPI 
Wnu5 <CMDTY> HCPI 
Wnu5 <CMDTY> HCPI 
Wnu5 <CMDTY> HCPI 
Wnu5 <CMDTY> HCPI 
Wnu5 <CMDTY> HCPI 
Wnu5 <CMDTY> HCPI 
Xbu5 <CMDTY> HCPI 
Xbu5 <CMDTY> HCPI 
Xbu5  <INDEX> HCPI 
Xbu5 <INDEX> HCPI 
Xbu5  <INDEX> HCPI 
Xbu5 <INDEX> HCPI 
Xbu5 <CMDTY> HCPI 
Xbu5 <CMDTY> HCPI 
Xbu5 <CMDTY> HCPI 
Xbu5 <CMDTY> HCPI 
Xbu5 <CMDTY> HCPI 
Xbu5 <CMDTY> HCPI 
Xbu5 <CMDTY> HCPI 
Xmu5 <CMDTY> HCPI 
Xmu5 <CMDTY> HCPI 
Xmu5  <INDEX> HCPI 
Xmu5 <INDEX> HCPI 
Xmu5  <INDEX> HCPI 
Xmu5 <INDEX> HCPI 
Xmu5 <CMDTY> HCPI 
Xmu5 <CMDTY> HCPI 
Xmu5 <CMDTY> HCPI 
Xmu5 <CMDTY> HCPI 
Xmu5 <CMDTY> HCPI 
Xmu5 <CMDTY> HCPI 
Xmu5 <CMDTY> HCPI 
Z u5 <CMDTY> HCPI 
Z u5 <CMDTY> HCPI 
Z u5  <INDEX> HCPI 
Z u5 <INDEX> HCPI 
Z u5  <INDEX> HCPI 
Z u5 <INDEX> HCPI 
Z u5 <CMDTY> HCPI 
Z u5 <CMDTY> HCPI 
Z u5 <CMDTY> HCPI 
Z u5 <CMDTY> HCPI 
Z u5 <CMDTY> HCPI 
Z u5 <CMDTY> HCPI 
Z u5 <CMDTY> HCPI 

Here is my data within Cells E1:G14

Issue Name                      Ticker
F/c 90day euro$ futr  dec15  Edz5 xcme
F/c h-shares idx fut  jul15     Hcn5 xhkf
F/c mini msci emg mkt sep15     Mesu5ifus
F/c s&p/tsx 60 ix fut sep15     Ptu5 xmod
F/c russell 2000 mini sep15     Rtau5ifus
F/c swiss mkt ix futr sep15     Smu5 xeur
F/c us 10yr note(cbt) sep15     Tyu5 xcbt
F/c euro buxl 30y bnd sep15     Ubu5 xeur
F/c euro stoxx 50     sep15     Vgu5 xeur
F/c us ultra bond(cbt sep15     Wnu5 xcbt
F/c gasoline rbob fut sep15     Xbu5 xnym
F/c aust 10yr bond fut sep 15   Xmu5 xsfe
F/c ftse 100 idx fut  sep15     Z u5 ifll

But this is what I would like the output to be:

Edz5 <CMDTY> HCPI 
Hcn5 <INDEX> HCPI 
Mesu5 <INDEX> HCPI 
Ptu5 <INDEX> HCPI 
Rtau5 <INDEX> HCPI 
Smu5 <INDEX> HCPI 
Tyu5 <CMDTY> HCPI 
Ubu5 <CMDTY> HCPI 
Vgu5 <CMDTY> HCPI 
Wnu5 <CMDTY> HCPI 
Xbu5 <CMDTY> HCPI 
Xmu5 <CMDTY> HCPI 
Z u5 <CMDTY> HCPI 

I've tried putting a Next c after each if statement but that does not work.

Answer 1

I think what you need to do to fix this is an "exit For" in each of the If Statement, so that if it finds a match or a "true" it will exit the for and not compare any more on the internal D loop. You will also likely have to change the print commands to before this exit for or before the next loop starts.

For Each c In v

         For Each d In w
                            d = d.Text
                            bbv1 = " QR "
                            bbv2 = " HCPI "
            If InStr(1, d, "mini") Then

                c = Mid(c, 1, 5) & " <INDEX>" & bbv2 '& c.Offset(0, 3)
                    Debug.Print (c)
                    Exit For
            ElseIf InStr(1, d, "ix") Then
                    'Debug.Print (c)
                c = Mid(c, 1, 4) & " <INDEX>" & bbv2
                    Debug.Print (c)
                    Exit For
            Else
                c = Mid(c, 1, 4) & " <CMDTY>" & bbv2  
                 Debug.Print (c)
                 Exit For
            End If
            'Debug.Print (c)

            Next d
        Next c