2 replies
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.
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
Subscribe To Our Newsletter!
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