EirikDaude
12-08-2013, 12:52 AM
I have the following code to put in formulas to a workbook I am making:
Sub rettoppiark()
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim i As Integer, ws As Worksheet
For i = 1 To 53
Set ws = Worksheets(CStr(i))
With ws
.Unprotect
'.EnableCalculation = False
Call nyeFormlar(i)
'.EnableCalculation = True
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
.EnableSelection = xlUnlockedCells
End With
Next
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Sub nyeFormlar(i As Integer)
Dim e24 As String, h24 As String, i24 As String, j24 As String, k24 As String, l24 As String, m24 As String
h24 = "=tellAvvik($F$3:$H$23,Grenser!$B$3,Grenser!$B$2)"
i24 = "=tellAvvik($I$3:$I$23,Grenser!$B$7,Grenser!$B$6)"
j24 = "=tellAvvik($J$3:$J$23,Grenser!$B$7,Grenser!$B$6)"
k24 = "=COUNTIF($K$3:$K$23," > "&Grenser!$B$8)"
l24 = "=COUNTIF($L$3:$L$23," > "&Grenser!$B$8)"
m24 = "=tellAvvik($M$3:$O$23,Grenser!$B$5,Grenser!$B$4)"
If i = 1 Then
e24 = "=IF(ISERROR(LOOKUP(9.99999999999999E+307,$E$3:$E$23)),'" + CStr(53) + "'!$E$24,LOOKUP(9.99999999999999E+307,$E$3:$E$23))"
Else
e24 = "=IF(ISERROR(LOOKUP(9.99999999999999E+307,$E$3:$E$23)),'" + CStr(i - 1) + "'!$E$24,LOOKUP(9.99999999999999E+307,$E$3:$E$23))"
End If
With Worksheets(CStr(i))
.Range("E24").Formula = e24
.Range("H24").Formula = h24
.Range("I24").Formula = i24
.Range("J24").Formula = j24
.Range("K24").Formula = k24
.Range("L24").Formula = l24
.Range("M24").Formula = m24
End With
End Sub
The code works, sort of, except it seems to put in a boolean value instead of the formula in cell K24 and L24, and I get an arrow pointing out of cell E24, apparently because there is a circular reference there:
http://i.imgur.com/InSd6RVl.png (http://imgur.com/InSd6RV)
Have any of you guys got any suggestions on how I can avoid this? I.e. I want the formulas stored in the strings k24 and l24 put into the similarly named cells, and I don't want the arrow pointing out of cell E24 to show.
- edit - I am a moron, and obviously figured out my mistake right after posting the problem here (feel free to delete the thread if you wish):
Sub nyeFormlar(i As Integer)
Dim e24 As String, h24 As String, i24 As String, j24 As String, k24 As String, l24 As String, m24 As String
h24 = "=tellAvvik($F$3:$H$23,Grenser!$B$3,Grenser!$B$2)"
i24 = "=tellAvvik($I$3:$I$23,Grenser!$B$7,Grenser!$B$6)"
j24 = "=tellAvvik($J$3:$J$23,Grenser!$B$7,Grenser!$B$6)"
k24 = "=COUNTIF($K$3:$K$23," + Chr(34) + ">" + Chr(34) + "&Grenser!$B$8)"
l24 = "=COUNTIF($L$3:$L$23," + Chr(34) + ">" + Chr(34) + "&Grenser!$B$8)"
m24 = "=tellAvvik($M$3:$O$23,Grenser!$B$5,Grenser!$B$4)"
If i = 1 Then
If FyrsteVekeEr53(2013) Then
e24 = "=IF(ISERROR(LOOKUP(9.99999999999999E+307,$E$3:$E$23)),'53'!$E$24,LOOKUP(9.9 9999999999999E+307,$E$3:$E$23))"
Else
e24 = "=IF(ISERROR(LOOKUP(9.99999999999999E+307,$E$3:$E$23)),0,LOOKUP(9.9999999999 9999E+307,$E$3:$E$23))"
End If
Else
If FyrsteVekeEr53(2013) And i = 53 Then
e24 = "=IF(ISERROR(LOOKUP(9.99999999999999E+307,$E$3:$E$23)),0,LOOKUP(9.9999999999 9999E+307,$E$3:$E$23))"
Else
e24 = "=IF(ISERROR(LOOKUP(9.99999999999999E+307,$E$3:$E$23)),'" + CStr(i - 1) + "'!$E$24,LOOKUP(9.99999999999999E+307,$E$3:$E$23))"
End If
End If
With Worksheets(CStr(i))
.Range("E24").Formula = e24
.Range("H24").Formula = h24
.Range("I24").Formula = i24
.Range("J24").Formula = j24
.Range("K24").Formula = k24
.Range("L24").Formula = l24
.Range("M24").Formula = m24
End With
End Sub
Sub rettoppiark()
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim i As Integer, ws As Worksheet
For i = 1 To 53
Set ws = Worksheets(CStr(i))
With ws
.Unprotect
'.EnableCalculation = False
Call nyeFormlar(i)
'.EnableCalculation = True
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
.EnableSelection = xlUnlockedCells
End With
Next
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Sub nyeFormlar(i As Integer)
Dim e24 As String, h24 As String, i24 As String, j24 As String, k24 As String, l24 As String, m24 As String
h24 = "=tellAvvik($F$3:$H$23,Grenser!$B$3,Grenser!$B$2)"
i24 = "=tellAvvik($I$3:$I$23,Grenser!$B$7,Grenser!$B$6)"
j24 = "=tellAvvik($J$3:$J$23,Grenser!$B$7,Grenser!$B$6)"
k24 = "=COUNTIF($K$3:$K$23," > "&Grenser!$B$8)"
l24 = "=COUNTIF($L$3:$L$23," > "&Grenser!$B$8)"
m24 = "=tellAvvik($M$3:$O$23,Grenser!$B$5,Grenser!$B$4)"
If i = 1 Then
e24 = "=IF(ISERROR(LOOKUP(9.99999999999999E+307,$E$3:$E$23)),'" + CStr(53) + "'!$E$24,LOOKUP(9.99999999999999E+307,$E$3:$E$23))"
Else
e24 = "=IF(ISERROR(LOOKUP(9.99999999999999E+307,$E$3:$E$23)),'" + CStr(i - 1) + "'!$E$24,LOOKUP(9.99999999999999E+307,$E$3:$E$23))"
End If
With Worksheets(CStr(i))
.Range("E24").Formula = e24
.Range("H24").Formula = h24
.Range("I24").Formula = i24
.Range("J24").Formula = j24
.Range("K24").Formula = k24
.Range("L24").Formula = l24
.Range("M24").Formula = m24
End With
End Sub
The code works, sort of, except it seems to put in a boolean value instead of the formula in cell K24 and L24, and I get an arrow pointing out of cell E24, apparently because there is a circular reference there:
http://i.imgur.com/InSd6RVl.png (http://imgur.com/InSd6RV)
Have any of you guys got any suggestions on how I can avoid this? I.e. I want the formulas stored in the strings k24 and l24 put into the similarly named cells, and I don't want the arrow pointing out of cell E24 to show.
- edit - I am a moron, and obviously figured out my mistake right after posting the problem here (feel free to delete the thread if you wish):
Sub nyeFormlar(i As Integer)
Dim e24 As String, h24 As String, i24 As String, j24 As String, k24 As String, l24 As String, m24 As String
h24 = "=tellAvvik($F$3:$H$23,Grenser!$B$3,Grenser!$B$2)"
i24 = "=tellAvvik($I$3:$I$23,Grenser!$B$7,Grenser!$B$6)"
j24 = "=tellAvvik($J$3:$J$23,Grenser!$B$7,Grenser!$B$6)"
k24 = "=COUNTIF($K$3:$K$23," + Chr(34) + ">" + Chr(34) + "&Grenser!$B$8)"
l24 = "=COUNTIF($L$3:$L$23," + Chr(34) + ">" + Chr(34) + "&Grenser!$B$8)"
m24 = "=tellAvvik($M$3:$O$23,Grenser!$B$5,Grenser!$B$4)"
If i = 1 Then
If FyrsteVekeEr53(2013) Then
e24 = "=IF(ISERROR(LOOKUP(9.99999999999999E+307,$E$3:$E$23)),'53'!$E$24,LOOKUP(9.9 9999999999999E+307,$E$3:$E$23))"
Else
e24 = "=IF(ISERROR(LOOKUP(9.99999999999999E+307,$E$3:$E$23)),0,LOOKUP(9.9999999999 9999E+307,$E$3:$E$23))"
End If
Else
If FyrsteVekeEr53(2013) And i = 53 Then
e24 = "=IF(ISERROR(LOOKUP(9.99999999999999E+307,$E$3:$E$23)),0,LOOKUP(9.9999999999 9999E+307,$E$3:$E$23))"
Else
e24 = "=IF(ISERROR(LOOKUP(9.99999999999999E+307,$E$3:$E$23)),'" + CStr(i - 1) + "'!$E$24,LOOKUP(9.99999999999999E+307,$E$3:$E$23))"
End If
End If
With Worksheets(CStr(i))
.Range("E24").Formula = e24
.Range("H24").Formula = h24
.Range("I24").Formula = i24
.Range("J24").Formula = j24
.Range("K24").Formula = k24
.Range("L24").Formula = l24
.Range("M24").Formula = m24
End With
End Sub