Looping excel function using VBA

Hello,
Can anyone help me with this function that I want to loop:

=IF(A1/A8<=1;A1/A8;IF(A1/(SUM(A8:B8))<=1;1+A1/(SUM(A8:B8));IF(A1/(SUM(A8:C8))<=1;2+A1/(SUM(A8:C8));IF(A1/(SUM(A8:D8))<=1;3+A1/(SUM(A8:D8));"a lot"))))

Here;s exapmle:
examplee.xlsx

Instead of "a lot" it should expand this formula up to reaching (24 + 0,xx) value, and it should work in different cells.
So I was thinking about something like creating

=NEWFORMULA(A1;A8)

and then in VBA

Function NEWFORMULA(A As Range, B As Range)

and that's it.. I don't have idea how to go on with it, I think OFFSET could be useful but I don't have any specific idea how to use it. Maybe someone can give me a help and show me direction.

Thanks in advance,
Regards

Ok, but it gives me Value in the cell on the right from B, and I want to give me SUM of them B.Value + B.Offset(0,1).Value etc. if you know what I mean.

Thanks nathansav, but I want it to be short, so it can be counted quickly if I'll put few thousands of this functions. Here's something what I came up with, but it has circular reference:

Function NEWFUNCTION(A As Range, B As Range)

On Error Resume Next

SUMA = A.Value

For i = 0 To 24
Range(B).Offset(0, i).Select
SUMB = Selection.Value

If SUMA / SUMB <= 1 Then
    
    NEWFUNCTION = i + (SUMA / SUMB)
    Exit Function
Else
    NEWFUNCTION = 0
End If
Next i

End Function