Insert Rows, Copy & Pasting on VBA

Hi guys,

I was wondering if you can help me with this problem. I would like to solve this in VBA. Attached is an excel file where Sheet1 is the current state and Sheet2 is what I would like to achieve. Basically, in column A, for each cluster of rows with similar Numbers I will need to insert the same cluster of rows below. The Number will subsequently change to reflect a new starting digit before "-". All digits after "-" is similar to before. In column B, the Value in the inserted rows will be the same as previously, but all to be negative. Finally, in column C, I would just like the exact copy of the Description in the above cells to be pasted in the inserted rows in the same column. You can refer to the excel if I am not doing a good job in explaining my problem. It will be nice to be able to program the highlighting colour for the inserted cells as well. Thank you in advance.

Originally Posted by JBeaucaire

This seems to do what you've described.

Please Login or Register  to view this content.

Hi JBeaucaire,

Thanks for the reply. Your code works very well. Now I have a slight adaptation to the problem. The Column A Numbers in the inserted rows will now be tagged to the Description. Again, all numbers that occur after "-" is the same as the above cells, but numbers preceding "-" are now to be changed to reflect the alphabet sequence. For eg, if Description states "RECL FOR A" then the number before "-" should be 1. If Description states "RECL FOR B" then the number before "-" should be 2. I have attached the new excel file reflecting what I have just described for your reference. Would appreciate your guidance in this. Thank you.

Thank you JBeaucaire, just one more query before I close this thread. I would like to code 26 cases for the alphabetically-tagged Number instead of using Asc. Can you point me in the right direction for just 1 alphabet example? Thanks.

Originally Posted by JBeaucaire

I don't understand the question. A few examples?

Hi JBeaucaire, basically I don't want to use asc function to tag the alphanumeric sequence. Which means I would like to use If...ElseIf. For eg,

If arrOLD(Rw, 3) = "RECL FOR A" Then
arrNEW(Rw, 1) = "1" & newNUM
ElseIf arrOLD(Rw, 3) = "RECL FOR B" Then
arrNEW(Rw, 1) = "2" & newNUM

Of course, I've tried the above code but it doesn't work. Can you help me?

Cross post.

http://forum.chandoo.org/threads/rep...-in-vba.31940/