Kindly Requesting Help with Excel VBA

[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

 

[venkat1926]

I wonder whether you can try this (may be not efficient way)

find the unique number of part numbers by using advance filter and then use countif function and park the results wherever you want. . you can write a macro for tis.

 

[ALNIOS ANOGIS]

Hi
First. sort by two criteria ( pallet , part number )
Secondly, Apply subtotal.
Regards,Junho

 

[Brandon S]

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

H
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/tutorial...7fa-105527c433fc/c--implement-data-acces.aspx

 

[Brandon S]

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/aspnet/35318213/trying-to-learn-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/tutorial...fd-6ed19e0a3492/build-c-msinfo-webservic.aspx