VBA: Do Loop with If Then Else in the middle and a need to return to a specific spot

[RLCornish]

I have a worksheet that I have recorded my customers on. Each row is for a different customer. I offer a variety of products stored as columns in this worksheet. The customers can order any combination of these products. I am recording how many of each item they buy on the same row as their customer info. In another tab I have a table of the products with added details such as the sell price, UOM, SKU, etc. I am trying to figure out how to create a loop that will go row by row, look across the product columns (set as For i = 15 to 53) and if it finds the customer ordered that product record the quantity ordered and and product description, stored as the column header, in a template on a different sheet. The other sheets uses this info to pull the added details from my table to create a complete invoice.

The piece I have below is just a snippit of this but I'm having trouble getting the loop to work the way I'd like after copying the column header and pasting it on another sheet. I thought I had written it correctly to go back to the last active cell on the "Multi" worksheet. However, it keeps going to the first customer row, first product column. It is the movement, not the actions that are not working for me.

Any suggestions would be appreciated.

Dim curSpot As String


Sheets("Multi").Activate
Range("A2").Select
Do Until IsEmpty(ActiveCell)
For i = 15 To 17
Rows(ActiveCell.Row).Columns(i).Select
If (ActiveCell) >= 1 Then Cells(1, ActiveCell.Column).Copy
curSpot = ActiveCell
Sheets("Item-Packing Detail").Select
' This is where I will put the paste function once I have proof of concept worked out with the movement
Application.Goto Reference:=("curSpot")
Next i
Cells(ActiveCell.Row, 1).Select
ActiveCell.Offset(1, 0).Select
Loop
End Sub

 

[sericom]

It's easier to write the values directly instead of selecting, eg

With Sheets("Multi")
    lastRow = .Range("A" & Rows.Count).End(xlUp).Row
    For r = 2 To lastRow
        For c = 15 To 17
            If .Cells(r, c) >= 1 Then 
                Sheets("Item-Packing Detail").Cells(r2, c2) = .Cells(r, c).Value
            End If
        Next c
    Next r
End With

You just need to work out how to populate r2 and c2 for the destination cell.

 

[RLCornish]

It's easier to write the values directly instead of selecting, eg

With Sheets("Multi")
    lastRow = .Range("A" & Rows.Count).End(xlUp).Row
    For r = 2 To lastRow
        For c = 15 To 17
            If .Cells(r, c) >= 1 Then 
                Sheets("Item-Packing Detail").Cells(r2, c2) = .Cells(r, c).Value
            End If
        Next c
    Next r
End With

You just need to work out how to populate r2 and c2 for the destination cell.

I very much like and follow the much cleaner version of moving thru the rows and columns. The "Then" of my code copies the header from whatever column it find a quantity in and the plan was to paste that header over to the "Item-Packing Detail" WS, and you're right, I still need to work out how to get the copied value to the right location on the IPD WS, but lets say for example the quantity sold is found in found in r20, c33. After pasting in the IPD WS, how do I get back there (r20, c33 on the "Multi" WS) to let the loop run thru the remainder of that row and ultimately the remaining rows thru lastRow?

 

[sericom]

That code will keep moving through each cell.
If you explain clearly how you want the data placed on "Item-Packing Detail" sheet, someone should be able to help

 

[RLCornish]

I see what you're saying now about it moving thru the remainder of the line.

There are 2 pieces of data from the "Multi" WS that need to be populated on the "Item-Packing Detail" WS, the quantity purchased and the product description, i.e. the column header where the quantity if found. Clients can, and often do order multiple items so as the code moves thru that row it will need to send 2 data elements to the next available row in the "IPD" WS, starting at r20, c1 (this is the quantity field); the product description is the same row but c4.

 

[sericom]

Will it always start at row 20, or do you want the code to look for the first blank row in a specific column?

 

[RLCornish]

It will always start at a specific cell and go down from there.