Running a calculation function for n number of rows-CodePudding

I've just taken up VBA to automate a calculation just to eliminate the human error factor for a measurement data excel file. While I've 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, so I first thought i could just 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 much to me 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.

Since I started learning this to develop just this tool for now, I've been trying to find ways and debug solutions everywhere, and I think that results in some unnecessary Dim lines at the start but if it runs, I wasn't going to remove them..

My code looks like this:

'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

CodePudding user response：

Hi and welcome to stackoverflow, 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

EDIT: think indented your code like I did to make it more readable too (or like @Darren Bartrup-Cook did when editing your question)

CodePudding user response：

This is more of an example of how to calculate each row.
Just getting your code to work across multiple rows.

Cells
With...End With Statement

'No need for Sub to call function that doesn't return anything.... just write a sub.
Public Sub ISO16032()

    Dim LastRow As Long
    With ThisWorkbook.Worksheets("Sheet1")
        LastRow = .Cells(.Rows.Count, 10).End(xlUp).Row
        
        Dim RowCounter As Long
        Dim Result As Long
        Dim Note As String
        'Only calculate if there is data in rows 3 onwards.
        If LastRow >= 3 Then
            'Reset results on each pass.
            'Assuming -1 is an impossible answer so code knows
            'not to put anything on Case Else.
            Result = -1
            Note = ""
            'Cycle through each row and calculate.
            For RowCounter = 3 To LastRow
                Select Case .Cells(RowCounter, 6) 'Look at column F(column 6) on each row.
                    Case 10.6 To 200
                        Result = .Cells(RowCounter, 10)
                        Note = "No corrections."
                    Case 3.6 To 10.5
                        Result = .Cells(RowCounter, 11)
                        Note = "Correction per ISO16032"
                    Case 0.1 To 3.5
                        Result = .Cells(RowCounter, 12)
                        Note = "Correction limit set to 2,2 dB"
                    Case Else
                        Note = "Repeat measurement!"
                End Select
                'Place results on sheet.
                .Cells(RowCounter, 7) = IIf(Result >= 0, Result, "")
                .Cells(RowCounter, 8) = Note
            Next RowCounter
        End If
    End With

End Sub