Excel VB For loop is not iterating through if statement

Question

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
                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!!

Does outputX = outputX + 1 appear to execute? I ask because you only mention "missing out the other two output lines completely." Which doesn't include the increment of outputX — Conrad Frix
– Conrad Frix, Commented May 31, 2011 at 15:05
Yes it misses out the increment of outputX as well. It never progresses past: Worksheets("Quote").Cells(outputX, 2) = qty — Frank
– Frank, Commented May 31, 2011 at 15:34
I have update the code to take address bpgergo's point about the problems with my increments. — Frank
– Frank, Commented May 31, 2011 at 15:37
If you want to value of the cell don't rely on default properies, but show it with qty = CInt(Worksheets("ProductList").Cells(i, 3).value) — John Alexiou
– John Alexiou, Commented May 31, 2011 at 17:52

Tim Williams · Accepted Answer · 2011-05-31 18:58:40Z

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

bpgergo · Accepted Answer · 2011-05-31 15:05:13Z

2

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)

answered May 31, 2011 at 15:05

bpgergo

16.1k5 gold badges48 silver badges70 bronze badges

1 Comment

Frank Over a year ago

However, the original problem still stands, the breakpoints show it never gets past the line: Worksheets("Quote").Cells(outputX, outputY) = qty

Collectives™ on Stack Overflow

Excel VB For loop is not iterating through if statement

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related