I created the below code. Could you please help me how to create a loop in order to make it easier and faster?I want the below for 54 repeats

Question

Sub check10()

k = Cells(3, 3)

If Cells(4, 2) <= k Then

Cells(5, 2) = Cells(4, 2) / (Cells(3, 3) / 5)

ElseIf Cells(4, 2) <= k + Cells(3, 3).Offset(0, 1) Then

Cells(5, 2) = 1 * 5 + (Cells(4, 2) - k) / (Cells(3, 3).Offset(0, 1) / 5)

ElseIf Cells(4, 2) <= k + Cells(3, 3).Offset(0, 1) + Cells(3, 3).Offset(0, 2) Then

Cells(5, 2) = 2 * 5 + (Cells(4, 2) - k - Cells(3, 3).Offset(0, 1)) / (Cells(3, 3).Offset(0, 2) / 5)

ElseIf Cells(4, 2) <= k + Cells(3, 3).Offset(0, 1) + Cells(3, 3).Offset(0, 2) + Cells(3, 3).Offset(0, 3) Then

Cells(5, 2) = 3 * 5 + (Cells(4, 2) - k - Cells(3, 3).Offset(0, 1) - Cells(3, 3).Offset(0, 2)) / (Cells(3, 3).Offset(0, 3) / 5)

ElseIf Cells(4, 2) <= k + Cells(3, 3).Offset(0, 1) + Cells(3, 3).Offset(0, 2) + Cells(3, 3).Offset(0, 3) + Cells(3, 3).Offset(0, 4) Then

Cells(5, 2) = 4 * 5 + (Cells(4, 2) - k - Cells(3, 3).Offset(0, 1) - Cells(3, 3).Offset(0, 2) - Cells(3, 3).Offset(0, 3)) / (Cells(3, 3).Offset(0, 4) / 5)

..............................................................................

End If

End Sub

Answer 1

hello

To create a loop for repeating the code block 54 times, you can use a For loop. Here's an example of how you can modify your code to incorporate the loop:

In this modified code, a For loop is added to repeat the code block 54 times. The loop iterates from 1 to 54, and inside the loop, the condition check is performed using the SumOffset function. The SumOffset function calculates the cumulative sum of the offset values based on the iteration number.

Please note that in your original code, the calculation logic seemed to have a pattern where the multiplier for Cells(5, 2) increases by 1 for each additional offset. I have modified the calculation to match that pattern, but please double-check if it aligns with your desired calculation logic.

By using the For loop, you can avoid duplicating the code block 54 times, making the code easier to manage and faster to execute.

vba

Sub check10()
    Dim k As Variant
    Dim i As Integer
    
    k = Cells(3, 3)
    
    For i = 1 To 54 ' Repeat the code block 54 times
        
        If Cells(4, 2) <= k + SumOffset(i) Then
            Cells(5, 2) = (i - 1) * 5 + (Cells(4, 2) - k) / (Cells(3, 3).Offset(0, i) / 5)
            Exit For ' Exit the loop once the condition is met
        End If
        
    Next i
    
End Sub

Function SumOffset(iteration As Integer) As Double
    Dim sum As Double
    Dim j As Integer
    
    For j = 1 To iteration
        sum = sum + Cells(3, 3).Offset(0, j)
    Next j
    
    SumOffset = sum
End Function

Answer 2

Thank u very much for your answer. The problem solved.

The only issue I face with this now is that the code is executed only on the Cell (3,3). How can I repeat the same Loop (the same code) for the next cells (Cell(3,4), Cell(3,5),...) I tries to modify the Function [sum = sum + Cells(3, m).Offset(0, j)], but it didn't work.

Could you please help with this?

Thank u in advance.

Answer 3

Below you may see the final code and why it doesn't work with the repeated Cells in the 3rt row:

Sub check11()
    
    Dim k As Integer
    Dim i As Integer
    
      
    
    For k = 2 To 54
    For i = 0 To 54
    
        
        If Cells(4, k) <= SumOffset(i) Then
            Cells(5, k) = i * 5 + (Cells(4, k) - SumOffset(i - 1)) / (Cells(3, k + 1).Offset(0, i) / 5)
            Exit For
        End If
        
    
    Next i
    Next k
    
End Sub

Function SumOffset(iteration As Integer) As Double
    Dim sum As Double
    
        Dim j As Integer
    
    
    For j = 0 To iteration
    
        sum = sum + Cells(3, 3).Offset(0, j)
        
    Next j
    
    SumOffset = sum
End Function

Answer 4

I think I found the gap. see the updated code. How can I solve the Message: "Division by zero"??

Sub check11()
    
    Dim k As Integer
    Dim i As Integer
    
      
    
    For k = 2 To 54
    For i = 0 To 54
    
        
        If Cells(4, k) <= SumOffset(i) Then
            Cells(5, k) = i * 5 + (Cells(4, k) - SumOffset(i - 1)) / (Cells(3, k + 1).Offset(0, i) / 5)
            Exit For
        End If
        
    
    Next i
    Next k
    
End Sub
Function SumOffset(iteration As Integer) As Double
    Dim sum As Double
    
    Dim k As Integer
    Dim j As Integer
    
    For k = 2 To 54
    For j = 0 To iteration
    
        sum = sum + Cells(3, k).Offset(0, j)
        
    Next j
    Next k
    SumOffset = sum
End Function