如何减少VBA EXCEL中这段代码的行数？[已关闭]

km0tfn4u  于 2022-12-20  发布在  其他
关注(0)|答案(1)|浏览(146)

已关闭。此问题需要details or clarity。当前不接受答案。
**想要改进此问题？**添加详细信息并通过editing this post阐明问题。

2天前关闭。
Improve this question
我是VBA Excel语言的新手，所以，我正在尝试编写一段代码，这段代码对我来说太长了，执行代码需要很长时间，我需要帮助。我不知道从哪里开始纠正这个问题，指针会很感激，解决方案会很棒。有人能帮忙吗？提前感谢。
选择病例操作索引（选择器i（2））

Case "Division"
    
    Data = Data / 43200
    Data_2 = Data_2 / 43200
    
    grupos_k = Grupos_in(0)
    grupos_l = Grupos_in(1)
    grupos_m = Grupos_in(2)
    grupos_n = Grupos_in(3)
    
    g_i = ThisWorkbook.Sheets("Index").Cells(23, 3 + x_i).Value
    Linea1 = ThisWorkbook.Sheets("Index").Range("B22").Value
    Linea2 = ThisWorkbook.Sheets("Index").Range("B37").Value
    
        If Lineas_index(0) = Linea1 Then
        
            If g_i <> grupos_k Then
                
            Else:
            
                 If g_i = Grupos_in(0) Then

                    For x_a = 0 To 3
                    
                        Prom_a = ThisWorkbook.Sheets("Index").Cells(24 + x_a, 3 + x_i).Value
                        
                        AverageV_x = Application.WorksheetFunction.Average(Prom_a)
                        ThisWorkbook.Sheets("Index").Range("C9").Value = AverageV_x
                    
                    Next
                    
                    For x_a_2 = 4 To 7
                    
                        Prom_a_2 = ThisWorkbook.Sheets("Index").Cells(24 + x_a_2, 3 + x_i).Value
                        
                        AverageV_x_2 = Application.WorksheetFunction.Average(Prom_a_2)
                        ThisWorkbook.Sheets("Index").Range("C10").Value = AverageV_x_2
                
                    Next
                    
                    For x_a_3 = 8 To 11
                    
                        Prom_a_3 = ThisWorkbook.Sheets("Index").Cells(24 + x_a_3, 3 + x_i).Value
                        
                        AverageV_x_3 = Application.WorksheetFunction.Average(Prom_a_3)
                        ThisWorkbook.Sheets("Index").Range("C11").Value = AverageV_x_3

                    Next
                    
                 End If
            End If
           
           If g_i <> grupos_l Then
            
            Else:
            
                 If g_i = Grupos_in(1) Then

                    For x_b = 0 To 3
                    
                        Prom_b = ThisWorkbook.Sheets("Index").Cells(24 + x_b, 3 + x_i).Value
                        
                        AverageV_b = Application.WorksheetFunction.Average(Prom_b)
                        ThisWorkbook.Sheets("Index").Range("D9").Value = AverageV_b
                    
                    Next
                    
                    For x_b_2 = 4 To 7
                    
                        Prom_b_2 = ThisWorkbook.Sheets("Index").Cells(24 + x_b_2, 3 + x_i).Value
                        
                        AverageV_b_2 = Application.WorksheetFunction.Average(Prom_b_2)
                        ThisWorkbook.Sheets("Index").Range("D10").Value = AverageV_b_2
                
                    Next
                    
                    For x_b_3 = 8 To 11
                    
                        Prom_b_3 = ThisWorkbook.Sheets("Index").Cells(24 + x_b_3, 3 + x_i).Value
                        
                        AverageV_b_3 = Application.WorksheetFunction.Average(Prom_b_3)
                        ThisWorkbook.Sheets("Index").Range("D11").Value = AverageV_b_3

                    Next
                    
                 End If
            End If

           If g_i <> grupos_m Then
            
            Else:
            
                 If g_i = Grupos_in(2) Then

                    For x_c = 0 To 3
                    
                        Prom_c = ThisWorkbook.Sheets("Index").Cells(24 + x_c, 3 + x_i).Value
                        
                        AverageV_c = Application.WorksheetFunction.Average(Prom_c)
                        ThisWorkbook.Sheets("Index").Range("E9").Value = AverageV_b
                    
                    Next
                    
                    For x_c_2 = 4 To 7
                    
                        Prom_c_2 = ThisWorkbook.Sheets("Index").Cells(24 + x_c_2, 3 + x_i).Value
                        
                        AverageV_c_2 = Application.WorksheetFunction.Average(Prom_c_2)
                        ThisWorkbook.Sheets("Index").Range("E10").Value = AverageV_c_2
                
                    Next
                    
                    For x_c_3 = 8 To 11
                    
                        Prom_c_3 = ThisWorkbook.Sheets("Index").Cells(24 + x_c_3, 3 + x_i).Value
                        
                        AverageV_c_3 = Application.WorksheetFunction.Average(Prom_c_3)
                        ThisWorkbook.Sheets("Index").Range("E11").Value = AverageV_c_3

                    Next
                    
                 End If
            End If

           If g_i <> grupos_n Then
            
            Else:
            
                 If g_i = Grupos_in(3) Then

                    For x_d = 0 To 3
                    
                        Prom_d = ThisWorkbook.Sheets("Index").Cells(24 + x_d, 3 + x_i).Value
                        
                        AverageV_d = Application.WorksheetFunction.Average(Prom_d)
                        ThisWorkbook.Sheets("Index").Range("F9").Value = AverageV_d
                    
                    Next
                    
                    For x_d_2 = 4 To 7
                    
                        Prom_d_2 = ThisWorkbook.Sheets("Index").Cells(24 + x_d_2, 3 + x_i).Value
                        
                        AverageV_d_2 = Application.WorksheetFunction.Average(Prom_d_2)
                        ThisWorkbook.Sheets("Index").Range("F10").Value = AverageV_d_2
                
                    Next
                    
                    For x_d_3 = 8 To 11
                    
                        Prom_d_3 = ThisWorkbook.Sheets("Index").Cells(24 + x_d_3, 3 + x_i).Value
                        
                        AverageV_d_3 = Application.WorksheetFunction.Average(Prom_d_3)
                        ThisWorkbook.Sheets("Index").Range("F11").Value = AverageV_d_3

                    Next
                    
                 End If
            End If
        End If


        If Lineas_index(1) = Linea2 Then
        
            If g_i <> grupos_k Then
                
            Else:
            
                 If g_i = Grupos_in(0) Then

                    For x_a_L2 = 0 To 3
                    
                        Prom_a_L2 = ThisWorkbook.Sheets("Index").Cells(39 + x_a_L2, 3 + x_i).Value
                        
                        AverageV_x_L2 = Application.WorksheetFunction.Average(Prom_a_L2)
                        ThisWorkbook.Sheets("Index").Range("C14").Value = AverageV_x_L2
                    
                    Next
                    
                    For x_a_2_L2 = 4 To 7
                    
                        Prom_a_2_L2 = ThisWorkbook.Sheets("Index").Cells(39 + x_a_2_L2, 3 + x_i).Value
                        
                        AverageV_x_2_L2 = Application.WorksheetFunction.Average(Prom_a_2_L2)
                        ThisWorkbook.Sheets("Index").Range("C15").Value = AverageV_x_2_L2
                
                    Next
                    
                    For x_a_3_L2 = 8 To 11
                    
                        Prom_a_3_L2 = ThisWorkbook.Sheets("Index").Cells(39 + x_a_3_L2, 3 + x_i).Value
                        
                        AverageV_x_3_L2 = Application.WorksheetFunction.Average(Prom_a_3_L2)
                        ThisWorkbook.Sheets("Index").Range("C16").Value = AverageV_x_3_L2

                    Next
                    
                 End If
            End If
           
           If g_i <> grupos_l Then
            
            Else:
            
                 If g_i = Grupos_in(1) Then

                    For x_b_L2 = 0 To 3
                    
                        Prom_b_L2 = ThisWorkbook.Sheets("Index").Cells(39 + x_b_L2, 3 + x_i).Value
                        
                        AverageV_b_L2 = Application.WorksheetFunction.Average(Prom_b_L2)
                        ThisWorkbook.Sheets("Index").Range("D14").Value = AverageV_b_L2
                    
                    Next
                    
                    For x_b_2_L2 = 4 To 7
                    
                        Prom_b_2_L2 = ThisWorkbook.Sheets("Index").Cells(39 + x_b_2_L2, 3 + x_i).Value
                        
                        AverageV_b_2_L2 = Application.WorksheetFunction.Average(Prom_b_2_L2)
                        ThisWorkbook.Sheets("Index").Range("D15").Value = AverageV_b_2_L2
                
                    Next
                    
                    For x_b_3_L2 = 8 To 11
                    
                        Prom_b_3_L2 = ThisWorkbook.Sheets("Index").Cells(39 + x_b_3_L2, 3 + x_i).Value
                        
                        AverageV_b_3_L2 = Application.WorksheetFunction.Average(Prom_b_3_L2)
                        ThisWorkbook.Sheets("Index").Range("D16").Value = AverageV_b_3_L2

                    Next
                    
                 End If
            End If

           If g_i <> grupos_m Then
            
            Else:
            
                 If g_i = Grupos_in(2) Then

                    For x_c_L2 = 0 To 3
                    
                        Prom_c_L2 = ThisWorkbook.Sheets("Index").Cells(39 + x_c_L2, 3 + x_i).Value
                        
                        AverageV_c_L2 = Application.WorksheetFunction.Average(Prom_c_L2)
                        ThisWorkbook.Sheets("Index").Range("E14").Value = AverageV_b_L2
                    
                    Next
                    
                    For x_c_2_L2 = 4 To 7
                    
                        Prom_c_2_L2 = ThisWorkbook.Sheets("Index").Cells(39 + x_c_2_L2, 3 + x_i).Value
                        
                        AverageV_c_2_L2 = Application.WorksheetFunction.Average(Prom_c_2_L2)
                        ThisWorkbook.Sheets("Index").Range("E15").Value = AverageV_c_2_L2
                
                    Next
                    
                    For x_c_3_L2 = 8 To 11
                    
                        Prom_c_3 = ThisWorkbook.Sheets("Index").Cells(39 + x_c_3_L2, 3 + x_i).Value
                        
                        AverageV_c_3_L2 = Application.WorksheetFunction.Average(Prom_c_3_L2)
                        ThisWorkbook.Sheets("Index").Range("E16").Value = AverageV_c_3_L2

                    Next
                    
                 End If
            End If

           If g_i <> grupos_n Then
            
            Else:
            
                 If g_i = Grupos_in(3) Then

                    For x_d_L2 = 0 To 3
                    
                        Prom_d_L2 = ThisWorkbook.Sheets("Index").Cells(39 + x_d_L2, 3 + x_i).Value
                        
                        AverageV_d_L2 = Application.WorksheetFunction.Average(Prom_d_L2)
                        ThisWorkbook.Sheets("Index").Range("F14").Value = AverageV_d_L2
                    
                    Next
                    
                    For x_d_2_L2 = 4 To 7
                    
                        Prom_d_2_L2 = ThisWorkbook.Sheets("Index").Cells(39 + x_d_2_L2, 3 + x_i).Value
                        
                        AverageV_d_2_L2 = Application.WorksheetFunction.Average(Prom_d_2_L2)
                        ThisWorkbook.Sheets("Index").Range("F15").Value = AverageV_d_2_L2
                
                    Next
                    
                    For x_d_3_L2 = 8 To 11
                    
                        Prom_d_3_L2 = ThisWorkbook.Sheets("Index").Cells(39 + x_d_3_L2, 3 + x_i).Value
                        
                        AverageV_d_3_L2 = Application.WorksheetFunction.Average(Prom_d_3_L2)
                        ThisWorkbook.Sheets("Index").Range("F16").Value = AverageV_d_3_L2

                    Next
                    
                 End If
            End If
        End If
End Select
excel

1条答案

按热度按时间
a9wyjsp7

a9wyjsp71#

您可以继续缩短，但将for I = 0 t0 3, 4 to 7...转换为For I = 1 to 11，然后除以4，以确定将其放入哪一行。

Sub Testing2()
    
    Dim I As Long
    Dim X_I As Variant
    Dim MyWS As Worksheet
    Dim ColNum As Integer
    
    Set MyWS = ThisWorkbook.Sheets("Index")
    
    Data = Data / 43200
    Data_2 = Data_2 / 43200
    
    grupos_k = Grupos_in(0)
    grupos_l = Grupos_in(1)
    grupos_m = Grupos_in(2)
    grupos_n = Grupos_in(3)
    
    With MyWS
        
        g_i = MyWS.Cells(23, 3 + X_I).Value
        Linea1 = MyWS.Range("B22").Value
        Linea2 = MyWS.Range("B37").Value
        
        Select Case g_i
            Case grupos_k
                ColNum = 3
            Case grupos_l
                ColNum = 4
            Case grupos_m
                ColNum = 5
            Case grupos_n
                ColNum = 6
        End Select
            
        If Lineas_index(0) = Linea1 Then
                     
            For I = 0 To 3
                MyWS.Cells(9, ColNum).Value = AveRG(MyWS.Cells(24 + I, 3 + X_I))
            Next
            For I = 4 To 7
                MyWS.Cells(10, ColNum).Value = AveRG(MyWS.Cells(24 + I, 3 + X_I))
            Next
            For I = 8 To 11
                MyWS.Cells(11, ColNum).Value = AveRG(MyWS.Cells(24 + I, 3 + X_I))
            Next
            
        End If
        
        If Lineas_index(1) = Linea2 Then
                     
            For I = 0 To 3
                MyWS.Cells(14, ColNum).Value = AveRG(MyWS.Cells(39 + I, 3 + X_I))
            Next
            For I = 4 To 7
                MyWS.Cells(15, ColNum).Value = AveRG(MyWS.Cells(39 + I, 3 + X_I))
            Next
            For I = 8 To 11
                MyWS.Cells(16, ColNum).Value = AveRG(MyWS.Cells(39 + I, 3 + X_I))
            Next
            
        End If
    End With
    
End Sub
Function AveRG(RG As Range) As Double
    AveRG = Application.WorksheetFunction(RG)
End Function
赞(0）分享  回复(0）举报  2022-12-20
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com