## 2 replies

TrowaD

- Posts
- 2829
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- November 22, 2021

Hi Anup,

I find it hard to understand what you are going for.

What I read is that you have a cell with a date in it.

Cell A2 (row 2 as an example) has value 60 and in B2 the formula =B/12*9, which isn't a proper formula. Same goes for C2.

If you want to know how to get the number 9 and 3 when the date is 1st October 2021 located in cell D1 (for example), then try this:

=MONTH(D1)-1 to get to 9

=12-(MONTH(D1)-1) to get to 3

...

Maybe you mean for B2: = A2/12*(MONTH(D2)-1)

Am I close?

Best regards,

Trowa

I find it hard to understand what you are going for.

What I read is that you have a cell with a date in it.

Cell A2 (row 2 as an example) has value 60 and in B2 the formula =B/12*9, which isn't a proper formula. Same goes for C2.

If you want to know how to get the number 9 and 3 when the date is 1st October 2021 located in cell D1 (for example), then try this:

=MONTH(D1)-1 to get to 9

=12-(MONTH(D1)-1) to get to 3

...

Maybe you mean for B2: = A2/12*(MONTH(D2)-1)

Am I close?

Best regards,

Trowa

TrowaD

- Posts
- 2829
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- November 22, 2021

Hi Anup,

So you want you end result to look like this:

It not clear to me though, what you starting situation looks like.

If it looks like this:

Then use this code:

When your data looks like this:

Then use this code:

If you are unfamiliar with codes then read the following:

How to implement and run a code:

- From Excel hit Alt + F11 to open the “Microsoft Visual Basic” window.

- Go to the top menu in the newly opened window > Insert > Module.

- Paste the code in the big white field.

- You can now close this window.

- Back at Excel, hit Alt + F8 to display the available macro’s.

- Double-click the macro you wish to run.

Best regards,

Trowa

So you want you end result to look like this:

It not clear to me though, what you starting situation looks like.

If it looks like this:

Then use this code:

Sub RunMe() Dim x, lRow As Long lRow = Range("A" & Rows.Count).End(xlUp).Row For x = 1 To lRow Step 5 Range("A" & x, "B" & x).Copy Range("A" & x + 1, "B" & x + 4) Range("C" & x, "F" & x).Copy Range("C" & x + 1).PasteSpecial Transpose:=True Next x Application.CutCopyMode = False End Sub

When your data looks like this:

Then use this code:

Sub RunMe() Dim x, lRow As Long lRow = Range("A" & Rows.Count).End(xlUp).Row For x = 1 To lRow * 5 Step 5 Rows(x + 1 & ":" & x + 4).Insert Range("A" & x, "B" & x).Copy Range("A" & x + 1, "B" & x + 4) Range("C" & x, "F" & x).Copy Range("C" & x + 1).PasteSpecial Transpose:=True Next x Application.CutCopyMode = False End Sub

If you are unfamiliar with codes then read the following:

How to implement and run a code:

- From Excel hit Alt + F11 to open the “Microsoft Visual Basic” window.

- Go to the top menu in the newly opened window > Insert > Module.

- Paste the code in the big white field.

- You can now close this window.

- Back at Excel, hit Alt + F8 to display the available macro’s.

- Double-click the macro you wish to run.

**NOTE:**macro’s cannot be reversed using the blue arrows. Always make sure you save your file (or create a back up to be entirely sure) before running a code, so you can re-open your file if something unforeseen happens or you want to go back to the situation before the code was run.Best regards,

Trowa

Yes, it was a mistake and I wanted to divide the volume un A to B and C column. I was able to come up with the same formula. Thank you very much for your quick reply.

But I have now a new challenge. Can you please help me.

Suppose I have one row with data in column A,B,C,D,Eand F.

Now suppose the name in A and B has to be repeated 4 times in 4 rows but C D and E will come in different rows.

Eg I want my one row to be divided in 5 rows with the following combination.

1st row A B C

2nd row A B D

3rd row A B E

4th row A B F

I have like this thousands of rows for which I would like to then repeat this formula so that same thing happens to every different row

Could you please help.

Best Regards,

Anup