Search code examples
excelvba

Running a calculation function for n number of rows

I want to automate a calculation for a measurement data Excel file.

While I made the function work, I could only figure out how to do that for one row at a time. The calculation is specific to the data on every row.

I thought I could change the Range("J3") value to Range("J3:J52") for a capacity of 50 calculations.

How do I make the function calculate for every row, separately, using the data of said specific row?

It doesn't matter if it runs for all 50 rows or if I have to figure out some loop function to find how many rows to calculate for, as long as one button press in the end will make the magic happen.

I have included a screenshot of the sheet for reference, the main calculation is done in Excel, but what this is doing is choosing the correct option out of a few different correction calculations and explaining to the user why.

I think there are some unnecessary Dim lines at the start but if it runs, I wasn't going to remove them.

enter image description here

'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
Solution

  • I think that a simple loop, with the addition of an argument to your ISO function allows you to solve your problem like this

    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