Excel VB For loop is not iterating through if statement

I appreciate this is an amateurish question but I am not used to VB and its syntax.

I am trying to pull across information from one worksheet (ProductList) to another (Quote) based on whether or not there is a value in the quantity (QTY) column.

Here is my method:

Private Sub cmdProductListContinue_Click()

    'Declare variabless
    Dim i, duration, qty, outputX, outputY

    'Set initial values
    duration = 120 'Used to determine number of iterations in for loop (no. of QTY cells we are to check)
    i = 3 'Used as cell co-ordinates to pull information from
    outputX = 17 'Used as cell co-ordinates to output information

    'Populate invoice with product info by iterating through all QTY cells and pulling across info if needed
    For i = 3 To duration
        'Reset quantity to zero each time
        qty = 0
        'Set quantity to the value in the QTY cell
        Set qty = Worksheets("ProductList").Cells(i, 3)
            'If there is a quantity value present
            If qty > 0 Then
                'Insert quantity value into appropriate cell in quote sheet
                Worksheets("Quote").Cells(outputX, 2) = qty
                'Insert description into quote sheet
                Worksheets("Quote").Cells(outputX, 3) = Worksheets("ProductList").Cells(i, 2)
                'Insert unit price into quote sheet
            开发者_如何学C    Worksheets("Quote").Cells(outputX, 4) = Worksheets("ProductList").Cells(i, 4)
                'Increment the output co-ordinates to the next line
                outputX = outputX + 1
            End If
    Next i

    'Open quote sheet
    Sheets("Quote").Select

End Sub

Using breakpoints I can see that when there is a quantity value it successfully moves onto the first 'Then' statement but then seems to just return to the start of the loop, missing out the other two output lines completely.

Is my syntax correct? Am I missing something in my logic?

I appreciate it may be hard to think this through without having the sheets to see the data columns etc.

'i' is set to 3 as the Quantity column first value is in cell C,3 with the description in C,2 and price in C,4. These are then incremented through the loop.

Any help with this would be appreciated.

Thanks!!

Here you are assigning qty to an object (a range):

Set qty = Worksheets("ProductList").Cells(i, 3)

If you instead want to get the cell value then use:

qty = Worksheets("ProductList").Cells(i, 3).Value

"Set" is used when assigning an object, so you don't need it here. "Value" is the default property, but I prefer to include it anyway.

A slight reworking of your code:

Private Sub cmdProductListContinue_Click()

Dim i, duration, qty, outputX
Dim wsQuote As Worksheet, wsProd As Worksheet

    Set wsQuote = Worksheets("Quote")
    Set wsProd = Worksheets("ProductList")

    duration = 120
    outputX = 17

    For i = 3 To duration
        qty = wsProd.Cells(i, 3).Value
        If qty > 0 Then
            With wsQuote.Rows(outputX)
                .Cells(2).Value = qty
                .Cells(3).Value = wsProd.Cells(i, 2).Value
                .Cells(4).Value = wsProd.Cells(i, 4).Value
                outputX = outputX + 1
            End With
        End If
    Next i

    wsQuote.Activate

End Sub

Set qty = Worksheets("ProductList").Cells(i, i)

This! Will iterate over the diagonal of the sheet, that is like C3, D4, E5, etc.

You may want something like

Set qty = Worksheets("ProductList").Cells(i, 3)

or

Set qty = Worksheets("ProductList").Cells(3, i)

Also check other references to ProductList sheet (end of the lines):

'Insert description into quote sheet
Worksheets("Quote").Cells(outputX, outputY + 1) = Worksheets("ProductList").Cells(i, i - 1)
'Insert unit price into quote sheet
Worksheets("Quote").Cells(outputX, outputY + 2) = Worksheets("ProductList").Cells(i, i + 1)