Need Help writing a Macro for a Bill of Material

[Richardall]

I'm looking for a macro that will compress raw data into a flat file.



The idea is to turn the Raw Data on sheet 2 into the organized Finished Look on sheet 3. I've included a file with the raw data and what the finished data should look like.



Here is my interpretation of what needs to happen. I just don't know how to write the macro.




Info in cell A4 needs to stay in A4
Info in cell C4 needs to move to cell B4
Info in cell I4 needs to move to cell C4
Info in cell A5 needs to move to cell D4
If these is info in B6, then move it to E4

If there isn't info in B6, then move to A6 and start again at step one

If there is no more information, not sure how to check for that, then stop moving the data and remove all the blank lines.
I hope this is clear enough, if not please ask. Would appreciate any and all help. :banghead:

[Richardall]

This is the current extent of my code skills. I have ideas about what to do and what should happen, but I'm just beginning to learn the commands

I have a bunch of remarks in this to try to help better explain what I'm trying to make happed. There may be better ways to do this with loops in loops.



Sub Compress()
'start on Row 4, This needs to be some sort of a counter
'For next loop starting at 4?
'Keeps the starting cell in the same place
Selection.Cut Destination:=Range("A4")
Range("A4").Select
'Moves the description to the correct cell on row 4
Selection.Cut Destination:=Range("A4")
Range("C4").Select
'Moves the Qty Per to the correct cell on row 4
Selection.Cut Destination:=Range("B4")
Range("I4").Select
'Moves the Ref Des up on row (Row + 1) to the correct cell on row 4
Selection.Cut Destination:=Range("C4")
Range("A5").Select
'Here is where it gets tricky, real tricky...
'This is a loop to find all the Manufacturere Could be one or many.
'Look to see if there is a valuse in D4.
'D4 is the orginal line plus 2 (row + 2)
' If the cell is empty,
' If not, loop to the top, increment counters
' as needed and start again.
Selection.Cut Destination:=Range("D4")
Range("B6").Select
Selection.Cut Destination:=Range("E4")
Range("B7").Select
Selection.Cut Destination:=Range("F4")
Range("B8").Select
Selection.Cut Destination:=Range("G4")
' If we're done, delete the extra rows between
Rows("5:8").Select
Selection.Delete Shift:=xlUp
'Look to see if we're done. No more part numbers in Column A
'if no more numbers, were done!
'once the rows are deleted, increment the counter and start again.
'Loop to top
End Sub



Thank you

[OBP]

It would be better to "copy" the data to a new sheet that way you still have the original data to work with. I will post you a modified copyof your worksheet shortly with the required code ona command button.

[MOS MASTER]

Hi Richard, :yes

I've edited your post to include VBA tags. This makes the code more readable cause it's formattes as per VBIDE!

Just select your code in the code window and press the VBA button to include the tags.

HTH, :whistle:

[Richardall]

Does look much easiere to read. What do I press to do this, not sure I understood?

Richard

[OBP]

Richard, please find attached worksheet, it now has a Comand button on the Raw Data sheet. When you click the button the raw data is transferred to the finished look sheet.
For this to work without extra programming I have added "1" to the a3 on the finished look sheet. I have removed the data that you had on the finished look sheet.
It will currently transfer 50 "Products" to increase that just increase the first "for next loop" value to want you want.

[MOS MASTER]

Does look much easiere to read. What do I press to do this, not sure I understood?

Richard

Hi Richard, :yes

If you edit a post you see a toolbar on top of the editor window. On it is a VBA button (See Image)

Just select your code and press that button. your code will enclosed by these tags: [ vba ] [ / vba ] (Minus the spaces)

HTH, :whistle:

[Richardall]

I have a few small problems.

The data moves to the new page but is started on row 3, which replaces my headers? What varible do I adjust?

I see a counter fro 1 to 50. Is that the number of loops the runs or will the loops continue until all the data is moved to the second sheet? I ask because I reduced the page for my example. Some of our sheets may run 15000 lines.

I can't wait to see if this will crunch the whole data set in one try. I'll have to move this in with some other cod I've been working on and try.

Thank you very much.

[OBP]

First the outer loop of 1 to 50 is the one that handles how many products it checks for, so yes make it 1 to 25000.
Did you enter something in row 3 or 4, the xldown is s looking for the last line of text, you can go to the bottom of the pageand use xlup instead.

[Richardall]

Complete understood your first statement and had already found and changed it...

I'm very sorry, but I don't know what you mean in the second statement.

On the second sheet, the third row is empty but I would like to leave it that way as a header. I can fill in a period in the cell below product if that will solve it without changing code?

Richard

[Richardall]

I put a ' in cell A3 on the second sheet. With it there, the code worked well.

I used the trial data and it appeared to run just fine. I'll pull a full extract in the morning and try it again.

Thank you for all this help. This will complete the first part of this fun...

Next i'll have to split all those chuncks of data in the MFG collumns and rewrite them into new columns with text formatting. Then I'll rearrange everything again and have a file ready for inport into a new system.

I greatly appreciat your help.

Richard

[OBP]

A pleasure.

[Richardall]

Can you add comments to the code for me to better understand what it's doing? I'm trying to learn the VB and the comments help me.

I would appreciate it.

I ran the code on a full extract this morning and it worked fantastically.

Richard :thumb

[OBP]

Richard, I will post it later OK.