Commulative function in sequences Cells in the same row

Question

The code (with the SumOffset1) works perfectly and gives the correct result in the cell(5,2) running the function which it starts from the cell(3,3).Could you please help me how to modify the function so as to get the correct result in the cell(5,3) and the function starting from the cell(3,4) and so on.

Sub commulative()
   
    Dim i As Integer
    For i = 0 To 5
        If Cells(4, 2) <= SumOffset1(i) Then
            Cells(5, 2) = (i - 1) * 5 + (Cells(4, 2) - SumOffset1(i - 1)) / (Cells(3, i + 2) / 5)    
            Exit For
        End If     
    Next i
 
End Sub

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

With the above code the function summaries only the cells from (3,2) and (3,3) etc.I d like to have something as is below but it doesn’t work:

Sub commulative()
   
    Dim i As Integer 
    For i = 0 To 5
        If Cells(4, k) <= SumOffset1(i) Then       
            Cells(5, k)= (i - 1) * 5 + (Cells(4, k) - SumOffset1(i - 1)) / (Cells(3, i + k) / 5)                                 
            Exit For
        End If  
    Next i 

End Sub

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

Can you describe in words (or pictures) what you want to achieve? The code you posted does not make much sense. — z32a7ul, Jun 27 '23 at 11:29

score 0 · Answer 1 · answered Jun 27 '23 at 14:41

I don't want to modify other parts of your code (Integer is rarely useful, Long is the usual type for integers, SumOffset1 should probably be inlined, etc.) because I don't understand what it does and why that's good.

However, if the current code works for you an you only want to do the same for k = 2, 3, 4 ... (you did not mention but I guess until an empty cell is found), then you can modify it like this (I marked the two new lines with a comment "Added"):

Sub commulative()
   
    Dim i As Integer
    For i = 0 To 5
        Dim k As Long: For k = 2 To ActiveSheet.Cells(4, 2).End(xlToRight).Column ' Added
            If Cells(4, k) <= SumOffset1(i) Then
                Cells(5, k) = (i - 1) * 5 + (Cells(4, k) - SumOffset1(i - 1)) / (Cells(3, i + k) / 5)
                Exit For
            End If
        Next ' Added
    Next i

End Sub

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

You need to add k as a parameter to the function eg. `SumOffset1(iteration As Integer, k as Double)` — CDP1802, Jun 27 '23 at 15:57

Commulative function in sequences Cells in the same row

1 Answers1