vba - Big Excel : Makro doesn't run, Excel 没有响应-6ren

vba - Big Excel : Makro doesn't run, Excel 没有响应

转载作者：行者123 更新时间：2023-12-04 20:54:12

我有个问题。我有来自调查的数据，我正在尝试用它填充数据库。它适用于 8 个测试数据集。现在我有大约 1000 个数据集，它没有运行并且 excel 停止响应。该数据库有 18720 行和 61 列，每个单元格都必须计算。它的代码是:

Sub DataBase()

'Set my tables
    Dim Answers As ListObject
    Dim Table As ListObject
    Set Answers = Worksheets("quantitativ").ListObjects("DataQuant")
    Set Table = Worksheets("Database").ListObjects("Tabelle7")

'Set my Ranges for filters (Organizational level, Location, Function...)

    Set OrgRange = Answers.ListColumns(19).Range
    Set LocRange = Answers.ListColumns(20).Range
    Set FuncRange = Answers.ListColumns(22).Range
    Set TrainRange = Answers.ListColumns(23).Range
    Set InvRange = Answers.ListColumns(25).Range

'Set Ranges for Answers to Questions (Scale)
Set Q1 = Answers.ListColumns(26).Range
Set Q2 = Answers.ListColumns(27).Range
Set Q3 = Answers.ListColumns(28).Range
Set Q4 = Answers.ListColumns(29).Range
Set Q5 = Answers.ListColumns(30).Range
Set Q6 = Answers.ListColumns(31).Range
Set Q7 = Answers.ListColumns(32).Range
Set Q8 = Answers.ListColumns(33).Range
Set Q9 = Answers.ListColumns(34).Range
Set Q10 = Answers.ListColumns(35).Range

Set Q11 = Answers.ListColumns(36).Range
Set Q12 = Answers.ListColumns(37).Range
Set Q13 = Answers.ListColumns(38).Range
Set Q14 = Answers.ListColumns(39).Range
Set Q15 = Answers.ListColumns(40).Range
Set Q16 = Answers.ListColumns(41).Range
Set Q17 = Answers.ListColumns(42).Range
Set Q18 = Answers.ListColumns(43).Range
Set Q19 = Answers.ListColumns(44).Range
Set Q20 = Answers.ListColumns(45).Range

Set Q21 = Answers.ListColumns(46).Range
Set Q22 = Answers.ListColumns(47).Range
Set Q23 = Answers.ListColumns(48).Range
Set Q24 = Answers.ListColumns(49).Range
Set Q25 = Answers.ListColumns(50).Range
Set Q26 = Answers.ListColumns(51).Range
Set Q27 = Answers.ListColumns(52).Range
Set Q28 = Answers.ListColumns(53).Range
Set Q29 = Answers.ListColumns(54).Range
Set Q30 = Answers.ListColumns(55).Range

Set Q31 = Answers.ListColumns(56).Range
Set Q32 = Answers.ListColumns(57).Range
Set Q33 = Answers.ListColumns(58).Range
Set Q34 = Answers.ListColumns(59).Range
'Spalte 60 Textantwort
Set Q35 = Answers.ListColumns(61).Range
Set Q36 = Answers.ListColumns(62).Range
Set Q37 = Answers.ListColumns(63).Range
Set Q38 = Answers.ListColumns(64).Range
Set Q39 = Answers.ListColumns(65).Range
'Spalte 66 Textantwort
Set Q40 = Answers.ListColumns(67).Range

Set Q41 = Answers.ListColumns(68).Range
Set Q42 = Answers.ListColumns(69).Range
Set Q43 = Answers.ListColumns(70).Range
'Spalte 71 Textantwort
Set Q44 = Answers.ListColumns(72).Range
Set Q45 = Answers.ListColumns(73).Range
Set Q46 = Answers.ListColumns(74).Range
'Spalte 75 Textantwort
Set Q47 = Answers.ListColumns(76).Range
Set Q48 = Answers.ListColumns(77).Range
Set Q49 = Answers.ListColumns(78).Range
Set Q50 = Answers.ListColumns(79).Range

Set Q51 = Answers.ListColumns(80).Range
Set Q52 = Answers.ListColumns(81).Range
'Spalte 82 Textantwort
Set Q53 = Answers.ListColumns(83).Range
Set Q54 = Answers.ListColumns(84).Range
Set Q55 = Answers.ListColumns(85).Range
Set Q56 = Answers.ListColumns(86).Range
'Spalte 87 Textantwort
Set Q57 = Answers.ListColumns(88).Range
Set Q58 = Answers.ListColumns(89).Range
Set Q59 = Answers.ListColumns(90).Range
Set Q60 = Answers.ListColumns(91).Range

Set Q61 = Answers.ListColumns(92).Range
'Spalte 93 Sinnlos? (Textantwort)
'Spalte 94 Textantwort

'Row variables for For-Loop
Dim r As Long

    With Worksheets("Database")
        'Gehe alle Zeilen der Tabelle durch
        For r = 5 To Table.DataBodyRange.Rows.Count + 4

            'Q1
            .Cells(r, 9).Value = Application.WorksheetFunction.CountIfs(Q1, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q2
            .Cells(r, 10).Value = Application.WorksheetFunction.CountIfs(Q2, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q3
            .Cells(r, 11).Value = Application.WorksheetFunction.CountIfs(Q3, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q4
            .Cells(r, 12).Value = Application.WorksheetFunction.CountIfs(Q4, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q5
            .Cells(r, 13).Value = Application.WorksheetFunction.CountIfs(Q5, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q6
            .Cells(r, 14).Value = Application.WorksheetFunction.CountIfs(Q6, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q7
            .Cells(r, 15).Value = Application.WorksheetFunction.CountIfs(Q7, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q8
            .Cells(r, 16).Value = Application.WorksheetFunction.CountIfs(Q8, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q9
            .Cells(r, 17).Value = Application.WorksheetFunction.CountIfs(Q9, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

           'Q10
            .Cells(r, 18).Value = Application.WorksheetFunction.CountIfs(Q10, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

           'Q11
            .Cells(r, 19).Value = Application.WorksheetFunction.CountIfs(Q11, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q12
            .Cells(r, 20).Value = Application.WorksheetFunction.CountIfs(Q12, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q13
            .Cells(r, 21).Value = Application.WorksheetFunction.CountIfs(Q13, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q14
            .Cells(r, 22).Value = Application.WorksheetFunction.CountIfs(Q14, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q15
            .Cells(r, 23).Value = Application.WorksheetFunction.CountIfs(Q15, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q16
            .Cells(r, 24).Value = Application.WorksheetFunction.CountIfs(Q16, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q17
            .Cells(r, 25).Value = Application.WorksheetFunction.CountIfs(Q17, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q18
            .Cells(r, 26).Value = Application.WorksheetFunction.CountIfs(Q18, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q19
            .Cells(r, 27).Value = Application.WorksheetFunction.CountIfs(Q19, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

           'Q20
            .Cells(r, 28).Value = Application.WorksheetFunction.CountIfs(Q20, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

           'Q21
            .Cells(r, 29).Value = Application.WorksheetFunction.CountIfs(Q21, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q22
            .Cells(r, 30).Value = Application.WorksheetFunction.CountIfs(Q22, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q23
            .Cells(r, 31).Value = Application.WorksheetFunction.CountIfs(Q23, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q24
            .Cells(r, 32).Value = Application.WorksheetFunction.CountIfs(Q24, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q25
            .Cells(r, 33).Value = Application.WorksheetFunction.CountIfs(Q25, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q26
            .Cells(r, 34).Value = Application.WorksheetFunction.CountIfs(Q26, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q27
            .Cells(r, 35).Value = Application.WorksheetFunction.CountIfs(Q27, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q28
            .Cells(r, 36).Value = Application.WorksheetFunction.CountIfs(Q28, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q29
            .Cells(r, 37).Value = Application.WorksheetFunction.CountIfs(Q29, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q30
            .Cells(r, 38).Value = Application.WorksheetFunction.CountIfs(Q30, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q31
            .Cells(r, 39).Value = Application.WorksheetFunction.CountIfs(Q31, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q32
            .Cells(r, 40).Value = Application.WorksheetFunction.CountIfs(Q32, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q33
            .Cells(r, 41).Value = Application.WorksheetFunction.CountIfs(Q33, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q34
            .Cells(r, 42).Value = Application.WorksheetFunction.CountIfs(Q34, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q35
            .Cells(r, 43).Value = Application.WorksheetFunction.CountIfs(Q35, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q36
            .Cells(r, 44).Value = Application.WorksheetFunction.CountIfs(Q36, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q37
            .Cells(r, 45).Value = Application.WorksheetFunction.CountIfs(Q37, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q38
            .Cells(r, 46).Value = Application.WorksheetFunction.CountIfs(Q38, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q39
            .Cells(r, 47).Value = Application.WorksheetFunction.CountIfs(Q39, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

           'Q40
            .Cells(r, 48).Value = Application.WorksheetFunction.CountIfs(Q40, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

'Q41
            .Cells(r, 49).Value = Application.WorksheetFunction.CountIfs(Q41, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q42
            .Cells(r, 50).Value = Application.WorksheetFunction.CountIfs(Q42, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q43
            .Cells(r, 51).Value = Application.WorksheetFunction.CountIfs(Q43, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q44
            .Cells(r, 52).Value = Application.WorksheetFunction.CountIfs(Q44, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q45
            .Cells(r, 53).Value = Application.WorksheetFunction.CountIfs(Q45, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q46
            .Cells(r, 54).Value = Application.WorksheetFunction.CountIfs(Q46, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q47
            .Cells(r, 55).Value = Application.WorksheetFunction.CountIfs(Q47, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q48
            .Cells(r, 56).Value = Application.WorksheetFunction.CountIfs(Q48, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q49
            .Cells(r, 57).Value = Application.WorksheetFunction.CountIfs(Q49, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

           'Q50
            .Cells(r, 58).Value = Application.WorksheetFunction.CountIfs(Q50, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

'Q51
            .Cells(r, 59).Value = Application.WorksheetFunction.CountIfs(Q51, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q52
            .Cells(r, 60).Value = Application.WorksheetFunction.CountIfs(Q52, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q53
            .Cells(r, 61).Value = Application.WorksheetFunction.CountIfs(Q53, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q54
            .Cells(r, 62).Value = Application.WorksheetFunction.CountIfs(Q54, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q55
            .Cells(r, 63).Value = Application.WorksheetFunction.CountIfs(Q55, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q56
            .Cells(r, 64).Value = Application.WorksheetFunction.CountIfs(Q56, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q57
            .Cells(r, 65).Value = Application.WorksheetFunction.CountIfs(Q57, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q58
            .Cells(r, 66).Value = Application.WorksheetFunction.CountIfs(Q58, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

            'Q59
            .Cells(r, 67).Value = Application.WorksheetFunction.CountIfs(Q59, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

           'Q60
            .Cells(r, 68).Value = Application.WorksheetFunction.CountIfs(Q60, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

'Q61
            .Cells(r, 69).Value = Application.WorksheetFunction.CountIfs(Q61, _
        .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
        .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))

        Next r

    End With


End Sub

有什么方法可以改进代码以使其运行，或者你们中的任何人有其他想法来解决它吗？
非常感谢。

最佳答案

应该可以简化为这样的代码:

我使用了一个数组来存储问题的列号，这样我们就可以轻松循环。我还添加了状态栏更新，以便您可以查看正在处理的行。由于有很多迭代，它仍然需要时间。

我还添加了一个计时器，因此您将看到处理一行的平均时间和处理所有行的估计时间。
但请注意，状态栏更新会增加额外的(甚至是最小的)负载。

Public Sub DataBase()
    'Set my tables
    Dim Answers As ListObject
    Dim Table As ListObject
    Set Answers = Worksheets("quantitativ").ListObjects("DataQuant")
    Set Table = Worksheets("Database").ListObjects("Tabelle7")

    'Set my Ranges for filters (Organizational level, Location, Function...)
    Set OrgRange = Answers.ListColumns(19).Range
    Set LocRange = Answers.ListColumns(20).Range
    Set FuncRange = Answers.ListColumns(22).Range
    Set TrainRange = Answers.ListColumns(23).Range
    Set InvRange = Answers.ListColumns(25).Range

    'Set Ranges for Answers to Questions (Scale)
    Dim QuestionColumns As Variant
    QuestionColumns = Array(26, 27, 28, 29, 30, 31, 32, 33, 34, 35, _
                            26, 37, 38, 39, 40, 41, 42, 43, 44, 45, _
                            46, 47, 48, 49, 50, 51, 52, 53, 54, 55, _
                            56, 57, 58, 59, 61, 62, 63, 64, 65, 67, _
                            68, 69, 70, 72, 73, 74, 76, 77, 78, 79, _
                            80, 81, 83, 84, 85, 86, 88, 89, 90, 91, _
                            92)


    Dim QuestionColumn As Variant
    Dim c As Long

    Dim rStart As Long: rStart = 5
    Dim rEnd As Long: rEnd = Table.DataBodyRange.Rows.Count + 4

    Dim StartTime As Double
    StartTime = Timer

    Application.ScreenUpdating = False
    Application.Calculation = xlManual

    With Worksheets("Database")
        Dim r As Long
        For r = rStart To rEnd 'Gehe alle Zeilen der Tabelle durch
            'update statusbar
            Application.StatusBar = "Processing Row " & CStr(r) & _
                                    ", Runtime: " & Format(Timer - StartTime, "0.00s") & _
                                    ", Time per row: " & Format((Timer - StartTime) / (r - rStart + 1), "0.00s") & _
                                    ", Estimated time: " & Format((Timer - StartTime) / (r - rStart + 1) * (rEnd - rStart), "0.00s") & _ 
                                    ", Time left: " & Format(((Timer - StartTime) / (r - rStart + 1) * (rEnd - rStart)) - (Timer - StartTime), "0.00s")
            DoEvents

            c = 9
            For Each QuestionColumn In QuestionColumns                    
                .Cells(r, c).Value = Application.WorksheetFunction.CountIfs(Answers.ListColumns(CLng(QuestionColumn)).Range, _
                                     .Cells(r, 8), OrgRange, .Cells(r, 1), LocRange, .Cells(r, 2), FuncRange, _
                                     .Cells(r, 4), InvRange, .Cells(r, 7), TrainRange, .Cells(r, 5))
                c = c + 1
            Next QuestionColumn
        Next r
    End With

    Application.StatusBar = "Done."

    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True
End Sub

关于vba - Big Excel : Makro doesn't run, Excel 没有响应，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/51780200/

文章推荐： excel - 基于百分比增加的条件格式

文章推荐： python - 使用 pandas 将两个 Excel 表与 Python 连接起来

文章推荐： vba - 使用 VBA Like 运算符比较两个变量中的值

文章推荐：即使值完全匹配，VBA 宏也找不到匹配项

big-o - 谁能解释一下 Big O、Big Omega 和 Big Theta？
这个问题已经有答案了: 已关闭10 年前。 Possible Duplicate: Big Theta Notation - what exactly does big Theta represent
big-o - 如何评估两个函数的 Big-Theta 和 Big-Omega 是否相等？
我有一个作业要证明这些是对还是错: a) 150n^3 + 43n^2 + 50^n + 3 = Ω(n^5) b) n^10 + 30n^8 + 80n^6 = O(n^12) c) 55n + 3
pointers - big.Int 和 *big.Int 之间的区别，以及如何按值传递 big.Int
我可以在 big.Int 上使用像 Text() 这样的方法，它工作正常，但是如果我返回一个 big.Int 然后使用“myfunc().Text()”会抛出一个错误，而如果我返回一个 * big.I
PHP 脚本 : How big is too big?
我正在用 PHP 开发一个网络应用程序，此时核心库的大小为 94kb。虽然我认为我现在是安全的，但多大才算太大？脚本的大小是否会成为一个问题，如果是这样，可以通过将脚本拆分为多个库来改善这一点吗？我
Big-O/Big-Oh 符号问题
我正在复习 Big-Oh 符号，但我在理解这个问题的解决方案时遇到了问题: Is 2n + 10 ≡ O(n)? Can we find c and n0? 2n + 10 = 10 n >= 10/
big-o - 哪个 Big-O 渐近增长得更快
我最近陷入了争论/辩论中，我试图对正确的解决方案做出明确的判断。众所周知， n! grows very quickly ，但究竟有多快，足以“隐藏”可能添加到其中的所有其他常量？让我们假设我有这个
big-o - 如何为我的循环找到 Big-O 符号？
我很难找出这段代码的 Big-O 符号。我需要找到两个 for 循环的符号。 public static int fragment(int n) { int sum = 0; for (in
big-o - 对数基础在 Big O 统治中重要吗？
给定两个函数: f(n)=O(log2n) 和 g(n)=O(log10n) 其中一个是否支配另一个？最佳答案请记住，任何碱基的对数都可以转换为仅以常数变化的公共(public)碱基。因此它们都
big-o - 使用 big-o 进行时间复杂度分析
经过修改，我们得出结论，时间复杂度实际上是O(2^n) 问题是时间复杂度是多少？是 O(2^n) 还是？我相信这是因为 for 循环被认为运行了 n 次。然后嵌套的 while 循环运行 2^n 次
big-o - 什么是嵌套循环的 Big-O，其中内循环的迭代次数由外循环的当前迭代确定？
以下嵌套循环的 Big-O 时间复杂度是多少: for (int i = 0; i < N; i++) { for (int j = i + 1; j < N; j++) {
c - 在 C 中按值传递参数 : how big is too big?
我很想知道经验丰富的 C 程序员认为可以按值传递的参数大小的上限是什么。上下文:我有机会使用 2×2 矩阵，它位于一个结构体中: typedef struct { double a, b, c,
c - 在 C 中按值传递参数 : how big is too big?
我很想知道经验丰富的 C 程序员认为可以按值传递的参数大小的上限是什么。上下文:我有机会使用 2×2 矩阵，它位于一个结构体中: typedef struct { double a, b, c,
big-o - Big-O = x 何时被归类为无效率？
假设我们有一个问题，我们使用 X 算法实现了 O(n) 或 O(log n) 或 etc...。 n 的值何时大到我们必须考虑替代实现？让我们看看我是否可以更好地解释自己。 For n=10,000
big-o - 这属于什么 Big-O 表示法？
这属于哪种 Big-O 表示法？我知道 setSearch() 和 removeAt() 是 O(n) 的顺序(假设它们是任意一种)。我知道如果没有 for 循环它肯定是 O(n)，但是我很困惑如何计
big-o - 两个非嵌套循环的 Big Oh 表示法
这是我的问题，我已经设法为 a 部分提出了一个答案，但对于 b 部分，我对 b 部分的答案并不是很自信。在最近的一起法庭案件中，一名法官以蔑视城市为由，下令第一天罚款 2 美元。之后的每一天，直到
algorithm - Big-O/Big-Oh 表示法
我正在尝试计算以下算法的大 O，但我很困惑，需要一些帮助: Algorithm 1. DFS(G,n) Input: G- the graph n- the current node 1
big-ip - 有什么方法可以模拟 F5 BIG-IP 服务器？
我们有一个使用 F5 BIG-IP 服务器进行负载平衡的潜在客户端。在确定我们是否可以将我们的产品与他们的负载均衡器干净地集成时，我开始查看 F5 提供的 API。问题是，如果没有 F5 服务器，我无
react-big-calendar - react-big-calendar 事件的基本设置未显示
我正在尝试使用 react-big-calendar 包。 http://intljusticemission.github.io/react-big-calendar/examples/index.
java - 递归方法的 Big-O 和 Big-Omega
我的任务是尝试找到给定 Java 方法的 big-O 和 big-Omega，但不知道如何找到。我知道 big-O 给出了上限，big-Omega 给出了下限，但是在查看程序(更不用说递归程序)时，我
algorithm - 如何对渐近符号函数集进行操作，即。 Big-O + Big-Omega？
我正在尝试确定以下陈述是对还是错。如果 f(n) ∈ O(n) 且 g(n) ∈ Ω(n)，则 f(n) + g(n) ∈ Θ(n)。我想我理解添加相同的渐近 big-O。 O(n) + O(n)

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

vba - Big Excel : Makro doesn't run, Excel 没有响应