excel - 运行 n 行的计算函数

Question

我想自动计算测量数据 Excel 文件。
虽然我使该功能正常工作，但我只能弄清楚如何一次只做一排。计算特定于每一行的数据。
我以为我可以更改 Range("J3")值为 Range("J3:J52")可进行 50 次计算。
如何使用所述特定行的数据分别计算每一行的函数？
不管它是否针对所有 50 行都运行，或者我是否必须找出一些循环函数来找到要计算的行数，只要最后按下一个按钮就会发生奇迹。
我已经附上了一张表格的截图以供引用，主要计算是在 Excel 中完成的，但是这样做是从几个不同的校正计算中选择正确的选项并向用户解释原因。
我认为一开始有一些不必要的暗线，但如果它运行，我不会删除它们。

'The main function, activated by a simple button Sub'
Function ISO16032()

    'DeltaL Range'
    Dim DeltaL As Range
    Set DeltaL = Range("F3")
    'Result is the corrected value in G column'
    Dim Result As Long
    'Note is the calc note in H column'
    Dim Note As String
    'X is the DeltaL between noise and background noise'
    Dim x As Long
    x = Range("F3").Value
    
    Select Case Range("F3").Value
    
        'No correction when X = > 10'
        Case 10.6 To 200
            Result = Range("J3")
            Range("G3").Value = Result
            Note = "No correction"
            Range("H3").Value = Note
        
        'Correction according to ISO16032 when X = between 4 and 10'
        Case 3.6 To 10.5
            Result = Range("K3")
            Range("G3").Value = Result
            Note = "Correction per ISO16032"
            Range("H3").Value = Note
        
        'Maximal correction value set to 2,2 dB if X < 4'
        Case 0.1 To 3.5
            Result = Range("L3")
            Range("G3").Value = Result
            Note = "Correction limit set to 2,2 dB"
            Range("H3").Value = Note
        
        'If x = < 0, the measurement is invalid'
        Case Else
            Note = "Repeat measurement!"
            Range("H3").Value = Note
    
    End Select

End Function

Answer 1

嗨，欢迎来到 stackoverflow，我认为一个简单的循环，在你的 ISO 函数中添加一个参数，可以让你像这样解决你的问题

Sub Looping()
    For i = 3 To 52
        ' Convert i to String because we need to concatenate with the letter F, G, H...
        Call ISO16032(CStr(i))
    Next
End Sub

Function ISO16032(Cell_X)
   
    'DeltaL Range'
    Dim DeltaL As Range
    Set DeltaL = Range("F" + Cell_X)
    'Result is the corrected value in G column'
    Dim Result As Long
    'Note is the calc note in H column'
    Dim Note As String
    'X is the DeltaL between noise and background noise'
    Dim x As Long
    x = Range("F" + Cell_X).Value
    
    Select Case Range("F" + Cell_X).Value
    
        'No correction when X = > 10'
        Case 10.6 To 200
            Result = Range("J" + Cell_X)
            Range("G" + Cell_X).Value = Result
            Note = "No correction"
            Range("H" + Cell_X).Value = Note
        
        'Correction according to ISO16032 when X = between 4 and 10'
        Case 3.6 To 10.5
            Result = Range("K" + Cell_X)
            Range("G" + Cell_X).Value = Result
            Note = "Correction per ISO16032"
            Range("H" + Cell_X).Value = Note
        
        'Maximal correction value set to 2,2 dB if X < 4'
        Case 0.1 To 3.5
            Result = Range("L" + Cell_X)
            Range("G" + Cell_X).Value = Result
            Note = "Correction limit set to 2,2 dB"
            Range("H" + Cell_X).Value = Note
        
        'If x = < 0, the measurement is invalid'
        Case Else
            Note = "Repeat measurement!"
            Range("H" + Cell_X).Value = Note

    End Select

End Function

编辑:想像我一样缩进你的代码以使其更具可读性(或者像@Darren Bartrup-Cook 在编辑你的问题时所做的那样)