+ Reply to Thread
Results 1 to 4 of 4

Finding the start of non-zero value

Hybrid View

  1. #1
    Registered User
    Join Date
    08-14-2011
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    63

    Finding the start of non-zero value

    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...
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    09-29-2011
    Location
    Kolkata, India
    MS-Off Ver
    Excel 2003/2007
    Posts
    182

    Re: Finding the start of non-zero value

    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
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    10-02-2012
    Location
    Bumi Nusantara
    MS-Off Ver
    Excel 2010; Excel 2016
    Posts
    136

    Re: Finding the start of non-zero value

    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
    click the star if it solves your problem

  4. #4
    Registered User
    Join Date
    08-14-2011
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    63

    Re: Finding the start of non-zero value

    Thanks Dwint!

    Your solution works combined with my code..

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1