Code stuck in an infinite loop

[Steve2612]

Hi Guys

I could really use your help. I have a macro that consists of several loops which up until recently was working perfectly. All of a sudden it looks as though the code is getting stuck in an infinite loop and i think i may have too many loops in my code combined with a poor structure. Here is my code, if anyone could help i would sincerely appreciate it as I am a novice when it comes to VBA.

Thanks in advance.

Here is my code:

For i = 1000 To 1 Step -1
     If (Cells(i, 1).Value = "SEDOL CHANGE") Then
     Cells(i, 9).FormulaArray = "=MIN(IF(INTRA!C[2]=Target!R" & i & "C6,INTRA!C[16]))"
     End If
   Next i
    
      For i = 1000 To 1 Step -1
       If (Cells(i, 1).Value = "SEDOL CHANGE") Then
      Cells(i, 9).Copy
      Cells(i, 9).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
    Application.CutCopyMode = False
     End If
Next i
    
    For i = 1000 To 1 Step -1
        If (Cells(i, 1).Value = "SEDOL CHANGE") Then
        Cells(i, 9).NumberFormat = "yyyy-mm-dd;@"
        End If
    Next i
    
    ' Calculate the LONG, BORROWED, LENT Positions
    ''First Find the LONG Positions
    
    For i = 1000 To 1 Step -1
        If (Cells(i, 1).Value = "SEDOL CHANGE") Then
        Cells(i, 10).FormulaR1C1 = _
        "=SUM(SUMIFS(INTRA!C[10],INTRA!C[1],Target!R" & i & "C6,INTRA!C[-9],{""GF0876"",""GF0878"",""GF0877""},INTRA!C[-6],""B""))"
        End If
    Next i
    
        For i = 1000 To 1 Step -1
        If (Cells(i, 1).Value = "SEDOL CHANGE") Then
        Cells(i, 10).Copy
        Cells(i, 10).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
            Application.CutCopyMode = False
            'change font colour to dark red
            Cells(i, 10).Select
            With Selection.Font
                .ThemeColor = xlThemeColorAccent2
                .TintAndShade = -0.249977111117893
            End With
        End If
    Next i
   
   For i = 1000 To 1 Step -1
        If (Cells(i, 1).Value = "SEDOL CHANGE") Then
        Cells(i, 10).NumberFormat = "#,##0"
        End If
    Next i
    
    '''Now Find the BORROWED Positions
    
    For i = 1000 To 1 Step -1
        If (Cells(i, 1).Value = "SEDOL CHANGE") Then
        Cells(i, 11).FormulaR1C1 = _
        "=SUM(SUMIFS(INTRA!C[9],INTRA!C[0],Target!R" & i & "C6,INTRA!C[-10],{""GFBOX"",""GFDTC"",""GFECLR""},INTRA!C[-7],""L""))-Target!R" & i & "C13"
        End If
    Next i
    
        For i = 1000 To 1 Step -1
        If (Cells(i, 1).Value = "SEDOL CHANGE") Then
        Cells(i, 11).Copy
        Cells(i, 11).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
            Application.CutCopyMode = False
            'change font colour to dark red
            Cells(i, 11).Select
            With Selection.Font
                .ThemeColor = xlThemeColorAccent2
                .TintAndShade = -0.249977111117893
            End With
        End If
    Next i
   
   For i = 1000 To 1 Step -1
        If (Cells(i, 1).Value = "SEDOL CHANGE") Then
        Cells(i, 11).NumberFormat = "#,##0"
        End If
    Next i
    
    ''''Now Find the LENT Positions
    
    For i = 1000 To 1 Step -1
        If (Cells(i, 1).Value = "SEDOL CHANGE") Then
        Cells(i, 12).FormulaR1C1 = _
        "=SUM(SUMIFS(INTRA!C[8],INTRA!C[-1],Target!R" & i & "C6,INTRA!C[-11],{""GFBOX"",""GFDTC""},INTRA!C[-8],""B""))"
        End If
    Next i
    
        For i = 1000 To 1 Step -1
        If (Cells(i, 1).Value = "SEDOL CHANGE") Then
        Cells(i, 12).Copy
        Cells(i, 12).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
            Application.CutCopyMode = False
            'change font colour to dark red
            Cells(i, 12).Select
            With Selection.Font
                .ThemeColor = xlThemeColorAccent2
                .TintAndShade = -0.249977111117893
            End With
        End If
    Next i
   
   For i = 1000 To 1 Step -1
        If (Cells(i, 1).Value = "SEDOL CHANGE") Then
        Cells(i, 12).NumberFormat = "#,##0"
        End If
    Next i
    
    
    
        For i = 1000 To 1 Step -1
        If (Cells(i, 1).Value = "SEDOL CHANGE") Then
        Cells(i, 13).Copy
        Cells(i, 13).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
            Application.CutCopyMode = False
            'change font colour to dark red
            Cells(i, 13).Select
            With Selection.Font
                .ThemeColor = xlThemeColorAccent2
                .TintAndShade = -0.249977111117893
            End With
        End If
    Next i
   
   For i = 1000 To 1 Step -1
        If (Cells(i, 1).Value = "SEDOL CHANGE") Then
        Cells(i, 13).NumberFormat = "#,##0"
        End If
    Next i
    
    End If
    
    Next

 

[Fluff]

Hia
You can remove some of those loops like this

    For i = 1000 To 1 Step -1
        If Cells(i, 1).Value = "SEDOL CHANGE" Then
            Cells(i, 9).FormulaArray = "=MIN(IF(INTRA!C[2]=Target!R" & i & "C6,INTRA!C[16]))"
            Cells(i, 10).FormulaR1C1 = _
                "=SUM(SUMIFS(INTRA!C[10],INTRA!C[1],Target!R" & i & "C6,INTRA!C[-9],{""GF0876"",""GF0878"",""GF0877""},INTRA!C[-6],""B""))"
            Cells(i, 11).FormulaR1C1 = _
                "=SUM(SUMIFS(INTRA!C[9],INTRA!C[0],Target!R" & i & "C6,INTRA!C[-10],{""GFBOX"",""GFDTC"",""GFECLR""},INTRA!C[-7],""L""))-Target!R" & i & "C13"
            With Range(Cells(i, 9), Cells(i, 11))
                .Copy
                .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
                Application.CutCopyMode = False
            End With
        End If
    Next i

Whilst I've just done the first 3 columns you can carry this on for the rest
HTH

 

[Steve2612]

Thanks Fluffy, really appreciate your help.

Still having the same issue, the problem may be from another section of code. Ill take another look...

Appreciate your help

 

[Roxxien]

To find your problematic loop, i would suggest you to execute them 1 by 1. For being to do that, put stop line between each loop by clicking on the little grey margin just at the left of your VBA screen then click F5 key to execute your code until the next stop line.

 

[Fluff]

As Roxxien has suggested you can you break points to find out where it's going wrong.
Also, how long have you let the macro run?
You are doing a lot of calculation so that me be a problem, have you turned calculation to manual?