B
Brandon S
Hello,
I am very new to Excel VBA and have had limited programming training. I have two issues that if solved can save me HOURS of work every day. If anyone out there could off their wisdom, I would be endlessly greatful!
I have an excel list of part numbers that are to go on a pallet and I need to print the total number of each part number to the right of my data. I struggled to create a macro that moves the subtotal over to the right of the rest of the data (and I am quite proud of that accomplishment); however because the part numbers are not necessarily listed in consecutive order, i.e. there could be three 50625's, one 55710, and then two more 50625's, I have two subtotals for 50625's under one pallet, which is not what I want (see screenshot link below). I cannot sort consecutively by the part number because there are up to 30 pallets listed one right after the other, so it would screw up which parts are on which pallet. This is the macro I am working with:
Sub movesubtotal()
For Each c In Range("c2:c2000")
If InStr((c.Value), "Count") Then
c.Cut Destination:=c.Offset(0, 5)
End If
Next
For Each d In Range("d2:d2000")
If InStr((d.Formula), "SUBTOTAL") Then
d.Cut Destination:=d.Offset(0, 5)
End If
Next
End Sub
Here is a screen of the print preview:
http://img267.yfrog.com/img267/1204/screenshotm.jpg
You can see my subtotals to the right (and I'm quite proud of acomplishing that )
The number in the far left column is the unique pallet ID number. When this changes, it is a new pallet. I also need to number the pallets to the left or right of the data (my second problem). The next column to the right is meaningless. The next colum to the right (third column from the left) is the part number.
The macro I tried to piece together for my second problem is as follows, albeit IT WAS A LONG SHOT:
Sub palletnum()
For Each e In Range("b2:b2000")
If ((e.Value) = 0) Then
Count = Count + 1
e.Print Count; e.Offset(0, -1)
End If
Next
End Sub
I don't even know if these commands are valid. I do know that this code doesn't even come close to working.
If anyone out there could help, I would cry I would be so happy. PLEASE AND THANK YOU!!
EggHeadCafe - Software Developer Portal of Choice
ASP.NET DropDown Menu Server Control
http://www.eggheadcafe.com/tutorial...1a-8ee78899a61c/aspnet-dropdown-menu-ser.aspx
I am very new to Excel VBA and have had limited programming training. I have two issues that if solved can save me HOURS of work every day. If anyone out there could off their wisdom, I would be endlessly greatful!
I have an excel list of part numbers that are to go on a pallet and I need to print the total number of each part number to the right of my data. I struggled to create a macro that moves the subtotal over to the right of the rest of the data (and I am quite proud of that accomplishment); however because the part numbers are not necessarily listed in consecutive order, i.e. there could be three 50625's, one 55710, and then two more 50625's, I have two subtotals for 50625's under one pallet, which is not what I want (see screenshot link below). I cannot sort consecutively by the part number because there are up to 30 pallets listed one right after the other, so it would screw up which parts are on which pallet. This is the macro I am working with:
Sub movesubtotal()
For Each c In Range("c2:c2000")
If InStr((c.Value), "Count") Then
c.Cut Destination:=c.Offset(0, 5)
End If
Next
For Each d In Range("d2:d2000")
If InStr((d.Formula), "SUBTOTAL") Then
d.Cut Destination:=d.Offset(0, 5)
End If
Next
End Sub
Here is a screen of the print preview:
http://img267.yfrog.com/img267/1204/screenshotm.jpg
You can see my subtotals to the right (and I'm quite proud of acomplishing that )
The number in the far left column is the unique pallet ID number. When this changes, it is a new pallet. I also need to number the pallets to the left or right of the data (my second problem). The next column to the right is meaningless. The next colum to the right (third column from the left) is the part number.
The macro I tried to piece together for my second problem is as follows, albeit IT WAS A LONG SHOT:
Sub palletnum()
For Each e In Range("b2:b2000")
If ((e.Value) = 0) Then
Count = Count + 1
e.Print Count; e.Offset(0, -1)
End If
Next
End Sub
I don't even know if these commands are valid. I do know that this code doesn't even come close to working.
If anyone out there could help, I would cry I would be so happy. PLEASE AND THANK YOU!!
EggHeadCafe - Software Developer Portal of Choice
ASP.NET DropDown Menu Server Control
http://www.eggheadcafe.com/tutorial...1a-8ee78899a61c/aspnet-dropdown-menu-ser.aspx