PDA

View Full Version : [SOLVED] I get a boolean value instead of formula with my code =/



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

snb
12-08-2013, 05:48 AM
Sub rettoppiark()
with Application
.ScreenUpdating = False
.EnableEvents = False

For i = 1 To 53
With sheets(format(i))
.Unprotect
'.application.EnableCalculation = False
nyeFormlar(i)
'.application.enablecalculation = True
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
.EnableSelection = xlUnlockedCells
End With
Next

.ScreenUpdating = True
.EnableEvents = True
end with
End Sub

But why entering formulae with VBA, since all formulae are fixed ?

EirikDaude
12-08-2013, 07:55 AM
Because I'm updating an existing workbook, and I figured it'd be faster to do it this way over the 53 sheets, since I'd have had to unprotect and protect the sheets one by one, and at least one of the formulas differed slightly from sheet to sheet.

snb
12-08-2013, 08:34 AM
If you unprotect all sheets you can use (manually of with VBA ) sheet.fillacrosssheets

EirikDaude
12-08-2013, 09:00 AM
Ah, I didn't know about that function (though I've used the manual version). I'd still have to use the loop to unprotect/protect the worksheets and to create this formula though:

e24 = "=IF(ISERROR(LOOKUP(9.99999999999999E+307,$E$3:$E$23)),'" + CStr(i - 1) + "'!$E$24,LOOKUP(9.99999999999999E+307,$E$3:$E$23))"