Delete data in duplicate cells and replace text with last 4 digits

[Sybille]

Good morning Forum,

I need your help today to finalize a project and that would be a wonderful Christmas gift !

I am including a sample file that explains exactly what I want to achieve.

First, I need to replace some of my column headers with only the 4 last numbers of said headers.

Second, I have a code that copies a row as many times as needed, according to the year columns with data. Example: if I have amounts in the 2024 and 2025 columns, the code will create a second row so I have 2 rows of the same ID.
What I need is to delete the duplicate amounts for that same ID. See the attached file, it will be clearer...

If needed, here is the code I use to duplicate the rows with more than 1 year/amount:

Please Login or Register  to view this content.

Thank you so much in advance for your help and have a very great day!

[kaasplankje]

In your code, col has the number 131. That seems unlikely as there are no data that far to the right.
Further, I don't understand how the amounts ( 447.877 $ and twice 537.452 $) were calculated. Is there a formula for that or is this randomly chosen?
Regarding the years in row 8, is it not easier to just type the correct year?

[Sybille]

Hello Kaasplankje,

My original spreadsheet is far larger than the sample I created for this thread.

In column 131, I calculate the number of year columns with amounts, and the code creates the duplicate rows according to it.

The amounts are not formula, they are entered manually BEFORE I run the code to duplicate the rows.

Row 8: no, the replacement needs to be a part of the code. This spreadsheet is received from an external collaborator and I am building an formatting and "adjusting" macro.

[Marc L]

Tabernacle Sybille !

First, I need to replace some of my column headers with only the 4 last numbers of said headers.

According to your attachment just with this single VBA codeline : [D8:H8] = [IF({1},"'"&RIGHT(D8:H8,4))] !

[Sybille]

Hey Marc

Thank you but could you be a little more explicit with your codeline?

Thank you!

[Marc L]

According to your attachment this is just a codeline to include in your VBA procedure
in order to update the D8:H8 headers as requested
just reproducing what any Excel beginner can easily achieve with cells formulas …

[Sybille]

Marc, I didn't know you could just put a code line starting with [YourRange] like that. You just taught me something.
This part of the code is solved thank to you .

[Marc L]

It's the short syntax of the Evaluate method, a VBA help must see !

[kaasplankje]

Please have a look at the attached file and tell me if this is how it should behave. Changed col to 13 to convenience and added preceding years for routine to work.

20221202.xlsm

[Sybille]

Thank you very much kaasplankje

However, I am not the queen of VBA and I just know enough to work around my needs, picking stuff here and there on internet.

I tried your code and I need your help to correct the errors below:

1) the code correcting the column headers should apply only to 5 columns (from D to H in my example, and from DQ to DU in my original spreadsheet). Your code changes all the column headers from column 2 to the last column. I was able to correct the starting column but not the "until the last column" part:

2) when I run the code on my original spreadsheet, I must miss an adjustment because the second part of the code, where the amounts are adjusted in the duplicate rows, applies to the column B, C, D of my original spreadsheet, instead of the range from DQ to DU

3) I had to declare the variables, but I need you to confirm they are correct


Here are the changes I made:

Please Login or Register  to view this content.

Thank you again!

[kaasplankje]

1) Replace Set yearrange = datasheet.Cells(8, 121).Resize(, datasheet.Cells(8, 121).End(xlToRight).Column) by Set yearrange = datasheet.Cells(8, 121).Resize(, 5)

2) Make sure fy_column is set to 121 and ly_column to 126, and remove the lines that may change those values

3) You only need to declare your variables if you work in Option Explicit (top row= in your macro). It's relevant for speed/avoiding typos but not for functionality. Anyway, what you have typed seems right.

[Sybille]

Hello Kaasplankje,

So I tried to modify the code according to your last post. The first one, correcting the column headers, is solved.

I still have the same problem with the amount distribution though, and maybe I did not change the code correctly.

Here it is:

Please Login or Register  to view this content.

And it still "distributes" columns B, C and D values, instead of DS, DT and DU (for this specific example).

I attach screen shots so you can visualize the error.

Thank you so much for your time and help.

[kaasplankje]

Try this:

Please Login or Register  to view this content.

Note that this routine is not dynamic as we have now manually entered which columns it concerns.

[Sybille]

Good morning kaasplankje,

The new code doesn't work either. The amounts are copied except the last year.

However, I discovered something with the second version, with

Please Login or Register  to view this content.

See the image attached. The first project GOC3533196 is wrong (amounts disappear), but the second GOC4491081 is correct (amounts correctly distributed).

Screenshot 2022-12-08.png

How come?

Have a very good day !

[kaasplankje]

For this erroneous rows, what does col 131 read?
Are the columns before DQ affected?

[Sybille]

Good morning kaasplankje,

The columns before DQ are not affected.

Before I run the code, GOC3533196 row shows 3 in col 131 and GOC4491081 shows 2

After I run the code, GOC3533196 row shows 0 in col 131 and GOC4491081 shows 1

For your information, the formula in col 131 is as follows:

Please Login or Register  to view this content.

Since the GOC3533196 amoutns disappear instead of being distributed, it is normal that the result show 0.

However I just realized that this portion of the programming code could use some more in-depth work and I'm going to remove it from the current code. If needed I'll open another thread about it.

I therefore consider that you have solved my request since the fist part works perfectly.

Thank you very much for your patience and time and have a very happy holiday season.

[Marc L]

If needed I'll open another thread about it.

Then crystal clear explain the logic to follow without anything to guess, with details, samples, …

[Sybille]

Then crystal clear explain the logic to follow without anything to guess, with details, samples, …

Promise! I'll give all the details needed!