Finding the start of non-zero value

[a_driga]

Dear all,

I have multiple sets of data of a sales, and based on my method I would like to calculate the modified moving average. The data sets are for 52 weeks. The problem is there are some of the items which just start a sale in the last few weeks before the end of data set, and in order to get a more accurate figure I need to start my calculation on the week in which the item has been introduced.
At the moment, I always start to calculate the modified moving average from the beginning of the data sets (first week in 52 weeks data set)

To give a more clear picture, I have attached an excel file.

There are two tasks to be completed using vba
1. Finding at which week the start of any sell after a certain period of week prior to that week with zero sales
2. What is the distance from week's starting sell until the end of data set

In my excel sheet;
the calculation of modified moving average for item A starts at week 20, item B at week 30
the distance from starting week to the end of data set for item A is 24 weeks while item B is 14 weeks

Thanks in advance for any advise...

[taps]

Hi Friend,

Please see the attached file how your problem is solved by using few formulas. Hope you have got your answers .
If you are satisfied then mark the thread as SOLVED.

Regards
taps

[dwint]

write down this code in VBE module

Option Explicit

Sub StartWeek()
    Dim i, j, r, c, idxCol As Long
    Dim msg, msg1, msg2 As String
    
    r = Sheet1.Range("a1").End(xlDown).Row
    c = Sheet1.Range("a1").End(xlToRight).Column
    For i = 2 To r
        For j = 2 To c
            If Cells(i, j) > 0 Then
                msg1 = "Start week for " & Cells(i, 1).Value & " is " & Cells(1, j).Value
                msg2 = "Distance to the End is " & c - j + 1 & " week(s)"
                msg = msg & vbNewLine & msg1 & " - " & msg2
                Exit For
            End If
        Next j
    Next i
    MsgBox msg, vbOKOnly, "Result"
End Sub

[a_driga]

Thanks Dwint!

Your solution works combined with my code..