VBA code: replicate article information based on year+month combination

[etski]

Hi, I am new to the forum and have just begun to learn VBA. I have a task that I would like to automate but I do not even know where to start. I believe there must be an easy way to solve this but I am not that advanced yet so I guess I am making this more complicated than it actually is.

I would be very thankful for every advice!


CURRENT SITUATION:
I have placed the following scenario on Sheet1:

• a table with client number, article number, product category and amount. Below an example "input table" in columns C-F (in reality there are more rows)
• there are 5 different product categories and an article can only belong to one category
• cell A2 shows the current month in YYYYMM format (assumed now is Feb 2018)

Sheet1:

I need to convert this table on Sheet1 to another format. I have placed the required format on Sheet2 and it looks like this:



REQUIRED FORMAT

Sheet2:

Columns Client & Amount
1) The information can directly be taken from the input table on Sheet1 (Client, Amount)

Columns Category 1 - Category 5
2) The columns category 1 to category 5 correspond to the column "Product Category" in the input table on Sheet1 --> idea is to place the article code to the category column where it belongs, according to the information that we have in column E on Sheet 1. E.g. article QWERT belongs to Category 2 so article code QWERT will be placed in column F (=Category 2) on Sheet 2.

Columns Business Unit and Key Customer
3) Column Business Unit is always filled with the value "XYZ" and column Key Customer remains always blank

Column Month
4) The column Month needs to be in format YYYYMM. I need to consider which month it currently is and create rows for the current & preceding months of the current year.

• E.g. if now is February 2018, I need to have a row for January 2018 and February 2018, for each of the article lines that I have in the "input table" on Sheet1. These rows are otherwise identical - the only difference is the year+month combination.
• The trick is to replicate each article information line on Sheet1 as many times as the current month is. If it were December 2018, I would have twelve lines (one for each month of 2018) for one article information line on Sheet1.

EXAMPLE:
Assumed now is February 2018 (corresponds to value in cell A2 on Sheet1)
Below is how my "output" table on Sheet2 should look like:

Does anybody have an idea how I could easily solve this? I find it especially difficult to formulate a macro that replicates the article information based on the year+month combination... and to make it flexible so that it does not matter whether it is 2022 or 2018.

Many thanks in advance!!

Kind regards,
etski

 

[Rijnsent]

Hi Etski,
that's a well formulated and described question, so I've taken the time to write you a bit of code:

Sub ProcessData()

'Set references to sheets, so you don't have to repeat the name over and over
Set ShtSrc = Worksheets("Sheet1")
Set ShtDest = Worksheets("Sheet2")

'Deterime start and end of data
StartRw = 2
EndRw = ShtSrc.Range("C" & Cells.Rows.Count).End(xlUp).Row

'Output amount and location
RepMnth = ShtSrc.Range("A1").Value
Repeats = RepMnth Mod 100
Yr = (RepMnth - Repeats) / 100
ResRwStart = 2
ResRw = ResRwStart
ShtDest.Range("A" & ResRwStart & ":A" & Cells.Rows.Count).ClearContents

If Repeats < 0 Or Repeats > 12 Then
    'Error, not possible
    Exit Sub
End If

If EndRw >= StartRw Then
    'Loop through the data
    For Rw = StartRw To EndRw
        Cat = ShtSrc.Cells(Rw, 5).Value
        CatVal = Val(Right(Cat, 1))
        For i = 1 To Repeats
            ShtDest.Range("A" & ResRw).Value = "XYZ"
            ShtDest.Range("B" & ResRw).Value = Yr * 100 + i
            ShtDest.Range("C" & ResRw).Value = ShtDest.Range("C" & Rw).Value
            ShtDest.Cells(ResRw, 4 + CatVal).Value = ShtDest.Range("E" & Rw).Value
            ShtDest.Range("J" & ResRw).Value = ShtDest.Range("F" & Rw).Value
            ResRw = ResRw + 1
        Next i
    Next Rw
Else
    'No data, error
End If


End Sub

That should do the trick. You could put it in a module and run it, but to learn a bit from it I'd recommend stepping through it (with the F8 key) and checking out the results on the way (info in the sheets and in the VBA-Local variables). The two main instruments you'd need are a IF-THEN and a FOR-NEXT. For a starter, I'd recommend trying these free courses: Free VBA Training Course or Excel VBA Programming - a free course for complete beginners

Cheers,
Koen

 

[FatBoyClam]

ShtDest.Range("C" & ResRw).Value = ShtDest.Range("C" & Rw).Value
ShtDest.Cells(ResRw, 4 + CatVal).Value = ShtDest.Range("E" & Rw).Value
ShtDest.Range("J" & ResRw).Value = ShtDest.Range("F" & Rw).Value

Hi Koen, I think the references on the right side of the equals sign should be for ShtSrc not ShtDest.

 

[Rijnsent]

Hi Koen, I think the references on the right side of the equals sign should be for ShtSrc not ShtDest.

You are right, didn't test the code... Leaves some work for @etski
Cheers,
Koen

 

[FatBoyClam]

You are right, didn't test the code... Leaves some work for @etski
Cheers,
Koen

Ah, the old "broken code as a teaching tool" trick, very clever ?

 

[etski]

Hi guys! Wow, thanks a lot for the quick replies.

I wonder how to incorporate the article code/product category part in this code.

E.g. If I have an article code that belongs to category 4 it will then show up in column for category 4 on the output table.

Have a nice day

 

[Rijnsent]

Hi guys! Wow, thanks a lot for the quick replies.

I wonder how to incorporate the article code/product category part in this code.

E.g. If I have an article code that belongs to category 4 it will then show up in column for category 4 on the output table.

Have a nice day

In your example the product category is filled with a value in Sheet1. I assume you either entered that there or you used a VLOOKUP formula to look up the category in a table with product codes & category names. The code takes the category and extracts the last number as a value to put the result in the right column (see Cat and CatVal in my code).
Koen

 

[etski]

Hi Koen, for some reason that part of the code does not work?

And actually, in reality my product categories are not named from 1 to 5.. How should the code be written in a way that it is not dependent on the the last number of the category name? (Rather dependent on the actual name of the category)

Kind regards, etski

 

[FatBoyClam]

Hi guys! Wow, thanks a lot for the quick replies.

I wonder how to incorporate the article code/product category part in this code.

E.g. If I have an article code that belongs to category 4 it will then show up in column for category 4 on the output table.

Have a nice day

Hi Etsy.

The code accommodates that.

The assumption is that your real-world category codes are as in the example (i.e. Category 1 to Category 5, not - for example "Shoes", "Coats", "Hats" etc).

This line:

CatVal = Val(Right(Cat, 1))

picks the digit from the last character of the category name, and converts it to a numeric value.

This line:

ShtDest.Cells(ResRw, 4 + CatVal).Value = ShtSrc.Range("E" & Rw).Value

uses the "CatVal" variable to select the column to input the Article code into (4th column plus the value from the category, thus Column E for Category 1 to Column I for Category 5)

If your real-work category codes do not have a numerical identifier at the end of the category name, the code could readily be adapted to search the category headers on Sheet2 for the category value. Let us know whether this is the case or not.

Cheers
Cal

 

[FatBoyClam]

Cross posting there.

Try substituting this code in the relevant place of the original;

If EndRw >= StartRw Then
    'Loop through the data
    For Rw = StartRw To EndRw
        Cat = ShtSrc.Cells(Rw, 5).Value
        ColNum = ShtDest.Range("1:1").Find(What:=Cat, LookIn:=xlValues).column
        For i = 1 To Repeats
            ShtDest.Range("A" & ResRw).Value = "XYZ"
            ShtDest.Range("B" & ResRw).Value = Yr * 100 + i
            ShtDest.Range("C" & ResRw).Value = ShtSrc.Range("C" & Rw).Value
            ShtDest.Cells(ResRw, ColNum).Value = ShtSrc.Range("E" & Rw).Value
            ShtDest.Range("J" & ResRw).Value = ShtSrc.Range("F" & Rw).Value
            ResRw = ResRw + 1
        Next i
    Next Rw
Else
    'No data, error
End If

This finds the category value in the first line of the destination sheet and returns the column number for use in the loop.