Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Kindly Requesting Help with Excel VBA
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/tutorials...-menu-ser.aspx |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Kindly Requesting Help with Excel VBA
Hi
First. sort by two criteria ( pallet , part number ) Secondly, Apply subtotal. Regards,Junho |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem 2
Jeez, that was easy. I should have seen that you can subtotal by more then one criteria!
Regarding my pallet numbering problem... screenshot- http://img267.yfrog.com/img267/1204/screenshotm.jpg There is a row for every carton. In column A, each row lists the pallet ID number which is the same for every carton on that pallet, then there are two empty rows and the next pallet ID number starts (actually three empty rows after I insert my subtotals that I need). I need to insert a pallet count that displays five rows up and six columns right. New pallet ID numbers are three rows below previous pallet ID numbers, so this is my "g" variable below. I'm trying to compare "e" to "g" and if they are not the same and "g" is not a blank cell, I want it to print the variable "f," which is my pallet count, five rows up and six columns right I'm really struggling with this code after much research on the internet. I've had some programming, but none in excel VBA, so I'm sure that my syntax is ugly and/or way off, but I really want to learn this stuff because it interests me. Sub palletnum() Dim f As Double Dim g As Double f = 0 For Each e In Range("a2:a2000") g = e.Offset(3, 0) If ((e.Value < g.Value) And (g.Value < 0)) Then f = f + 1 Range("e.Offset(-5, -1)") = f End If Next End Sub Anyone that can help me, it would be very, very much appreciated!! Thanks! Brandon ALNIOS ANOGIS wrote: HiFirst. sort by two criteria ( pallet , part number )Secondly, Apply subtotal. 02-Nov-09 Hi First. sort by two criteria ( pallet , part number ) Secondly, Apply subtotal. Regards,Junho Previous Posts In This Thread: EggHeadCafe - Software Developer Portal of Choice C# : Implement Data Access Layer independent of Physical Database Schema http://www.eggheadcafe.com/tutorials...ata-acces.aspx |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
FYI - double post
Sorry for the double post. Please instead answer on the attached subject. I wanted to post it as a new question as to avoid all of the superfluous information above.
http://www.eggheadcafe.com/software/...rn-cannot.aspx Brandon S wrote: problem 2 03-Nov-09 Jeez, that was easy. I should have seen that you can subtotal by more then one criteria! Regarding my pallet numbering problem... screenshot- http://img267.yfrog.com/img267/1204/screenshotm.jpg There is a row for every carton. In column A, each row lists the pallet ID number which is the same for every carton on that pallet, then there are two empty rows and the next pallet ID number starts (actually three empty rows after I insert my subtotals that I need). I need to insert a pallet count that displays five rows up and six columns right. New pallet ID numbers are three rows below previous pallet ID numbers, so this is my "g" variable below. I'm trying to compare "e" to "g" and if they are not the same and "g" is not a blank cell, I want it to print the variable "f," which is my pallet count, five rows up and six columns right I'm really struggling with this code after much research on the internet. I've had some programming, but none in excel VBA, so I'm sure that my syntax is ugly and/or way off, but I really want to learn this stuff because it interests me. Sub palletnum() Dim f As Double Dim g As Double f = 0 For Each e In Range("a2:a2000") g = e.Offset(3, 0) If ((e.Value < g.Value) And (g.Value < 0)) Then f = f + 1 Range("e.Offset(-5, -1)") = f End If Next End Sub Anyone that can help me, it would be very, very much appreciated!! Thanks! Brandon Previous Posts In This Thread: EggHeadCafe - Software Developer Portal of Choice Build C# MSInfo WebService with COM Interop http://www.eggheadcafe.com/tutorials...webservic.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to appy the formular with different cell parameters~kindly hel | Excel Worksheet Functions | |||
vlookup doesn't work consistently (I think), kindly help | Excel Worksheet Functions | |||
how do I turn off requesting scan when opening Excel (Office XP) | Excel Discussion (Misc queries) | |||
TAB properties in a UserForm - Requesting Help from Excel VBA Guru | Excel Programming | |||
Experts kindly help | Excel Programming |