VBA Macro To Move Data To Summary Sheet Based On ID's

[spitz7985]

Hello,
I am having a very difficult time with this code. I have had several intro programming classes, but not with visual basic. I've noted the problem lines with comments below. The problems are probably very easy syntax error. I wouldn't be surprised if this code is not the most efficient way to go about this either, but like I said, I'm a noob. I've searched a lot, but have not had luck.

 
Sub list() 
     
    Dim counter As Integer 
    Dim counter2 As Integer 
    Dim id As Double 
    Dim id2 As Double 
    Dim palletnum As Integer 
     
    ActiveSheet. Name = "Pallet Detail" 
     Sheets.Add 
    ActiveSheet.Name = "Pallet Summary" 
    Sheets("Pallet Detail").Select 
     
     
    For Each z In Range("b2:b2000") 
        id = Range(z).Offset(0, -1).Value 'the range(z) does not work
        id2 = Range(z).Offset(3, -1).Value 'the range(z) does not work
         
        If InStr((z.Value), "Count") Then 
            counter = counter + 1 
            z.Cut Destination:=Sheets("Pallet Summary").Range("B" & counter) 
             
        End If 
         
        If id <> id2 Then 
            palletnum = palletnum + 1 
            Set Sheets("Pallet Summary").Range("A" & counter) = palletnum 'this is a problem line
             
        End If 
    Next 
     
     
    For Each y In Range("c2:c2000") 
         
        If InStr((y.Formula), " SUBTOTAL") Then 
            counter2 = counter2 + 1 
            y.Cut Destination:=Sheets("Pallet Summary").Range("C" & counter2) 
        End If 
         
    Next 
     
     
End Sub 
 

[norie]

Why are you using Range(z)?

Why not just z?

Why are you using Set here?

Set  Sheets("Pallet Summary").Range("A" & counter) = palletnum 

Perhaps this?

Option Explicit 
 
Sub CreateList() 
    Dim y As Range 
    Dim z As Range 
     
    Dim id As Double 
    Dim id2 As Double 
     
    Dim counter As Long 
    Dim counter2 As Long 
    Dim palletnum As Long 
     
    ActiveSheet.Name = "Pallet Detail" 
    Sheets.Add 
    ActiveSheet.Name = "Pallet Summary" 
     
    Sheets("Pallet Detail").Select 
     
     
    For Each z In Sheets("Pallet Detail").Range("B2:B2000") 
         
        id = z.Offset(0, -1).Value 'the range(z) does not work
        id2 = z.Offset(3, -1).Value 'the range(z) does not work
         
        If InStr(z.Value, "Count") Then 
            counter = counter + 1 
            z.Cut Destination:=Sheets("Pallet Summary").Range("B" & counter) 
             
        End If 
         
        If id <> id2 Then 
            palletnum = palletnum + 1 
            Sheets("Pallet Summary").Range("A" & counter) = palletnum 'this is a problem line
             
        End If 
         
    Next 
     
    For Each y In Sheets("Pallet Detail").Range("C2:C2000") 
         
        If InStr(y.Formula, " SUBTOTAL") Then 
            counter2 = counter2 + 1 
            y.Cut Destination:=Sheets("Pallet Summary").Range("C" & counter2) 
        End If 
         
    Next 
     
End Sub 

[spitz7985]

Hey Norie,
Thanks a lot!!

That's probably the reason I couldn't find anything about Range(variable) in my google searches, because you don't need the range()!

As far as the "set sheets..." line, well, what you corrected it to is what I started out with and it does not work for me. It is now the only line in the code holding me up. I thought maybe the "set" would set that cell equal to the palletnum variable, but I have not had luck with or without it.

[norie]

How is that part not working?

You only use Set when you want to create a reference to an object eg a range, worksheet etc.

[spitz7985]

Uh oh. I think my problem is much deeper then this single line.

I think my problem is my loop. First of all, I knew that it was hack job way of doing it, because I don't necessarily need to loop until b2000 (you programmers are going to hate me for this). I just went down that far because I knew b2000 would for sure include all of my data. I'm going to be using this macro on many spreadsheets of varying length. This type of loop was also the only way I knew how to set the z to each cell in the B column counting down one row each time the loop cycles (although I now am seeing that it's not working like I thought it was).

Bascially what I'm trying to do is pull all my count subtotals from a sheet and paste it in column B (stating "xxx Count") and C (stating the count value) on a new sheet that this macro is creating. This macro did do this, but the part with the id and id2 variables was supposed to also insert a value into column A of the new sheet, explanation below.

The spreadsheet I'm starting with has this information (this is for work):
(A)Pallet ID Number (B)useless number (C)-part number (D&E)- useless

There is a row for each box of each part, so there are many in a row with the same Pallet ID number and the same part number. There can also be multiple part numbers per pallet ID number. There are two blank rows after each pallet ID number before the next pallet ID number, so I need to compare the pallet ID number from the active row to the pallet ID number three rows down, and if it is different, I need to insert a pallet count on the new sheet that the macro creates in column A, counting from 1 to up to 30. I have the original sheet sorted by pallet ID and then by part number and have count subtotals after each part number (eventually I would like the macro to do this automatically as well, but I'm taking one step at a time here).

This is what I want the new sheet to look like:

(A)Pallet Number (count from 1 to up to 30)
(B)Part Number "Count" (statement)
(C)value of the count subtotal

[spitz7985]

Alright guys, I rewrote my loop and i"m very happy with it. However, I still have a problem with one of the if statements. I don't know if you can use the following or not, so I just did it the ugly way so I could be sure, but it still doesn't work. I'm sure that my "id" and "id2" variables are working perfectly because I printed them out and I can see the condition happeneing that my non-working if statement is supposed to be testing for.

Not sure if you can do this:

If id <> id2 And id <> 0 And id2 <> 0 Then 

My code:

Sub list() 
     
    Dim mycellA As  Range 
    Dim mycellB As Range 
    Dim mycellC As Range 
    Dim rangecounter As Integer 
    Dim L As Double 
    Dim L2 As Double 
    Dim Bcounter As Integer 
    Dim Ccounter As Integer 
    Dim id As Double 
    Dim id2 As Double 
    Dim palletnum As Integer 
     
    ActiveSheet.Name = "Pallet Detail" 
     Sheets.Add 
    ActiveSheet.Name = "Pallet Summary" 
    Sheets("Pallet Detail").Select 
     
    L = 1 
    rangecounter = 1 
     
    Do Until L = 0 And L2 = 0 
         
        rangecounter = rangecounter + 1 
        Set mycellB = Range("B" & rangecounter) 
         
        id = mycellB.Offset(0, -1).Value 
        id2 = mycellB.Offset(3, -1).Value 
         
        Sheets("Pallet Summary").Range("E" & rangecounter).Value = id 'TEST LINE
        Sheets("Pallet Summary").Range("F" & rangecounter).Value = id2 'TEST LINE
         
        If InStr((mycellB.Value), "Count") Then 
            Bcounter = Bcounter + 1 
            mycellB.Cut Destination:=Sheets("Pallet Summary").Range("B" & Bcounter) 
             
             '       ***********THIS BELOW IS THE PROBLEM IF STATEMENT********************
             
            If id <> id2 Then 
                If id <> 0 Then 
                    If id2 <> 0 Then 
                        palletnum = palletnum + 1 
                        Sheets("Pallet Summary").Range("A" & Bcounter) = palletnum 
                        Sheets("Pallet Summary").Rows(Bcounter + 1).EntireRow.Insert 
                    End If 
                End If 
            End If 
        End If 
         
        Set mycellC = Range("C" & rangecounter) 
         
        If InStr((mycellC.Formula), " SUBTOTAL") Then 
            Ccounter = Ccounter + 1 
            mycellC.Cut Destination:=Sheets("Pallet Summary").Range("C" & Ccounter) 
        End If 
         
        Set mycellA = Range("A" & rangecounter) 
        L = mycellA.Value 
        L2 = mycellA.Offset(1, 0).Value 
         
    Loop 
     
     
End Sub 

[norie]

I think you might want to chuck some parentheses in there, your logical statement might not actually be doing what you want.

Perhaps something like this.

If (id <> id2) And (id <> 0) And (id2 <> 0) Then 

I know that might seem a bit simple but without the parentheses you might actually find you are comparing the wrong things.

[Dave Hawley]

spitz7985, please consider those who search, You thread title SHOULD spell out what you are tryting to do, not what YOU THINK the answer is.