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