Search code examples
excelvbaplotinventory-management

How to generate accumulated inventory plot based on week number in Excel vba

I would like to draw automatically accumulated inventory plot using Excel VBA 2016.

I have inventory data for different year in the format of "20XXYY". Here "XX" indicates year and "YY" indicates week number in a year (1 to 52). I will have data similar to picture 1. Then I would like to sort it as picture 2. Finally draw plot as picture 3. However, I would like to have it automatically using VBA.

Picture 1

Picture 2

Picture 3

My question is that how can I create sequential YearWeek column automatically for the plot?

I have used "=SUM($Y$2:Y2)"^^ formula in "Accumulated Inventory" column. I will highly appreciate if I will get any clue.

Solution

  • 'This is the answer. I have figure it out. Thanks for your support. 

    Sub consecutiveNumber()

Dim wsFrom As Worksheet
Dim Com As String
Dim lLastNumber,FirstNumber, l, i As Long


'change to names of sheets you are coptying from and to

Set wsFrom = ThisWorkbook.Sheets("Sheet1")


'Get the value in the last and first used text box of column A

lLastNumber = wsFrom.Range("A" & Rows.Count).End(xlUp).Value
FirstNumber = wsFrom.Range("A1").Value
'clear column B

wsFrom.Range("B:B").Clear

'Initializing the cell index value
i = 1
'fill column B on sheet 1 with first to last number on column A

 For l = FirstNumber To lLastNumber

'Converting number to string
Com = Right(CStr(l), 2)

If CInt(Com) >= 53 Then
l = l + 48

    wsFrom.Range("M" & i) = l
    i = i + 1
 Else

    wsFrom.Range("M" & i) = l
    i = i + 1

  End If


Next

End Sub