TrowaD

- Posts
- 2691
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- January 14, 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
- 2691
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- January 14, 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.

**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

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.

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