2007 Excel VBA Macro som går veldig tregt

[Bigelk]

Hei.

 

Jeg har et par koder som jeg legger inn ved hjelp av en makro, men det går veldig tregt.

 

kode Nr1. Den Henter inn tall i fra syv ark og summerer de sammen.

=HVISFEIL(SUMMER.HVIS.SETT(INDIREKTE("02.01.2012!d$4:d$44");INDIREKTE("02.01.2012!a$4:a$44");A29);0)

+HVISFEIL(SUMMER.HVIS.SETT(INDIREKTE("03.01.2012!d$4:d$44");INDIREKTE("03.01.2012!a$4:a$44");A29);0)

+HVISFEIL(SUMMER.HVIS.SETT(INDIREKTE("04.01.2012!d$4:d$44");INDIREKTE("04.01.2012!a$4:a$44");A29);0)

+HVISFEIL(SUMMER.HVIS.SETT(INDIREKTE("05.01.2012!d$4:d$44");INDIREKTE("05.01.2012!a$4:a$44");A29);0)

+HVISFEIL(SUMMER.HVIS.SETT(INDIREKTE("06.01.2012!d$4:d$44");INDIREKTE("06.01.2012!a$4:a$44");A29);0)

+HVISFEIL(SUMMER.HVIS.SETT(INDIREKTE("07.01.2012!d$4:d$44");INDIREKTE("07.01.2012!a$4:a$44");A29);0)

+HVISFEIL(SUMMER.HVIS.SETT(INDIREKTE("08.01.2012!d$4:d$44");INDIREKTE("08.01.2012!a$4:a$44");A29);0)

 

 

Kode Nr2. deler en celle på en annen.

=HVIS(ERFEIL(E30/D30);"";E30/D30)

 

Det jeg lurer på er om dette rett og slett tar lang tid Pga formelene, eller om koden jeg har skrevet er dårlig og for tidkrevende.

 

Koden

Sheets("uke5").Select
Dim a As String
Dim b As String
Dim c As String
Dim d As String
Dim e As String
Dim f As String
Dim g As String
a = "02.01.2012"
b = "03.01.2012"
c = "04.01.2012"
d = "05.01.2012"
e = "06.01.2012"
f = "07.01.2012"
g = "08.01.2012"


Range("d4").Select
   Dim i As Integer
   For i = 1 To 131
       ActiveCell.FormulaR1C1 = _
       "=IFERROR(SUMIFS(INDIRECT(""" & a & "!d$4:d$44""),INDIRECT(""" & a & "!a$4:a$44""),RC[-3]),0) _
        +IFERROR(SUMIFS(INDIRECT(""" & b & "!d$4:d$44""),INDIRECT(""" & b & "!a$4:a$44""),RC[-3]),0) _
        +IFERROR(SUMIFS(INDIRECT(""" & c & "!d$4:d$44""),INDIRECT(""" & c & "!a$4:a$44""),RC[-3]),0) _
        +IFERROR(SUMIFS(INDIRECT(""" & d & "!d$4:d$44""),INDIRECT(""" & d & "!a$4:a$44""),RC[-3]),0) _
        +IFERROR(SUMIFS(INDIRECT(""" & e & "!d$4:d$44""),INDIRECT(""" & e & "!a$4:a$44""),RC[-3]),0) _
        +IFERROR(SUMIFS(INDIRECT(""" & f & "!d$4:d$44""),INDIRECT(""" & f & "!a$4:a$44""),RC[-3]),0) _
        +IFERROR(SUMIFS(INDIRECT(""" & g & "!d$4:d$44""),INDIRECT(""" & g & "!a$4:a$44""),RC[-3]),0)"
       ActiveCell.Offset(0, 1).Select
       ActiveCell.FormulaR1C1 = _
       "=IFERROR(SUMIFS(INDIRECT(""" & a & "!e$4:e$44""),INDIRECT(""" & a & "!a$4:a$44""),RC[-4]),0) _
        +IFERROR(SUMIFS(INDIRECT(""" & b & "!e$4:e$44""),INDIRECT(""" & b & "!a$4:a$44""),RC[-4]),0) _
        +IFERROR(SUMIFS(INDIRECT(""" & c & "!e$4:e$44""),INDIRECT(""" & c & "!a$4:a$44""),RC[-4]),0) _
        +IFERROR(SUMIFS(INDIRECT(""" & d & "!e$4:e$44""),INDIRECT(""" & d & "!a$4:a$44""),RC[-4]),0) _
        +IFERROR(SUMIFS(INDIRECT(""" & e & "!e$4:e$44""),INDIRECT(""" & e & "!a$4:a$44""),RC[-4]),0) _
        +IFERROR(SUMIFS(INDIRECT(""" & f & "!e$4:e$44""),INDIRECT(""" & f & "!a$4:a$44""),RC[-4]),0) _
        +IFERROR(SUMIFS(INDIRECT(""" & g & "!e$4:e$44""),INDIRECT(""" & g & "!a$4:a$44""),RC[-4]),0)"
       ActiveCell.Offset(0, 2).Select
       ActiveCell.FormulaR1C1 = _
       "=IFERROR(SUMIFS(INDIRECT(""" & a & "!g$4:g$44""),INDIRECT(""" & a & "!a$4:a$44""),RC[-6]),0) _
        +IFERROR(SUMIFS(INDIRECT(""" & b & "!g$4:g$44""),INDIRECT(""" & b & "!a$4:a$44""),RC[-6]),0) _
        +IFERROR(SUMIFS(INDIRECT(""" & c & "!g$4:g$44""),INDIRECT(""" & c & "!a$4:a$44""),RC[-6]),0) _
        +IFERROR(SUMIFS(INDIRECT(""" & d & "!g$4:g$44""),INDIRECT(""" & d & "!a$4:a$44""),RC[-6]),0) _
        +IFERROR(SUMIFS(INDIRECT(""" & e & "!g$4:g$44""),INDIRECT(""" & e & "!a$4:a$44""),RC[-6]),0) _
        +IFERROR(SUMIFS(INDIRECT(""" & f & "!g$4:g$44""),INDIRECT(""" & f & "!a$4:a$44""),RC[-6]),0) _
        +IFERROR(SUMIFS(INDIRECT(""" & g & "!g$4:g$44""),INDIRECT(""" & g & "!a$4:a$44""),RC[-6]),0)"
       ActiveCell.Offset(0, 2).Select
       ActiveCell.FormulaR1C1 = _
       "=IFERROR(SUMIFS(INDIRECT(""" & a & "!i$4:i$44""),INDIRECT(""" & a & "!a$4:a$44""),RC[-8]),0) _
        +IFERROR(SUMIFS(INDIRECT(""" & b & "!i$4:i$44""),INDIRECT(""" & b & "!a$4:a$44""),RC[-8]),0) _
        +IFERROR(SUMIFS(INDIRECT(""" & c & "!i$4:i$44""),INDIRECT(""" & c & "!a$4:a$44""),RC[-8]),0) _
        +IFERROR(SUMIFS(INDIRECT(""" & d & "!i$4:i$44""),INDIRECT(""" & d & "!a$4:a$44""),RC[-8]),0) _
        +IFERROR(SUMIFS(INDIRECT(""" & e & "!i$4:i$44""),INDIRECT(""" & e & "!a$4:a$44""),RC[-8]),0) _
        +IFERROR(SUMIFS(INDIRECT(""" & f & "!i$4:i$44""),INDIRECT(""" & f & "!a$4:a$44""),RC[-8]),0) _
        +IFERROR(SUMIFS(INDIRECT(""" & g & "!i$4:i$44""),INDIRECT(""" & g & "!a$4:a$44""),RC[-8]),0)"
       ActiveCell.Offset(0, -5).Select
       ActiveCell.Offset(1, 0).Select
   Next i

Range("f4").Select
   For i = 1 To 131
       ActiveCell.FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[-2]),"""",RC[-1]/RC[-2])"
       ActiveCell.Offset(0, 2).Select
       ActiveCell.FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[-4]),"""",RC[-1]/RC[-4])"
       ActiveCell.Offset(0, 2).Select
       ActiveCell.FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[-6]),"""",RC[-1]/RC[-6])"
       ActiveCell.Offset(0, 1).Select
       ActiveCell.FormulaR1C1 = "=IF(ISERROR(RC[-6]/RC[-4]),"""",RC[-6]/RC[-4])"
       ActiveCell.Offset(0, 1).Select
       ActiveCell.FormulaR1C1 = "=IF(ISERROR(RC[-7]/RC[-3]),"""",RC[-7]/RC[-3])"
       ActiveCell.Offset(0, 1).Select
       ActiveCell.FormulaR1C1 = "=IF(ISERROR(RC[-6]/RC[-4]),"""",RC[-6]/RC[-4])"
       ActiveCell.Offset(0, -7).Select
       ActiveCell.Offset(1, 0).Select
   Next i

Jeg sitter på et intranett, så "pcene" som gjør selve utregninga er jo et par mil unna. Kan det være derfor?

 

 

Hilsen

 

Torbjørn

[Moskus]

Hvis du heller bruker VB-funksjoner istedenfor Excel-formler i macroen din går det normalt kjappere...