Code not working

[dnicholsby]

Hello,

I have written some code by I keep getting an error "Next without For". Any help appreciated:

Sub SelectStocks()


Dim i As Integer
Dim r As Integer
Dim region As String
Dim StockCount As Integer
Dim lastrow As Integer
Dim Ticker As String
Dim destCell As range


lastrow = Cells(Rows.Count, "A").End(xlUp).Row


For i = 50 To 59
For r = 18 To lastrow


StockCount = Cells(38, i).Value
If StockCount = 0 Then Next i


region = Cells(17, i)


    ElseIf Cells(1, r).Offset(0, 4) = region Then
        If Cells(1, r).Offset(0, 17) <= StockCount Then
        Ticker = Cells(1, r).Value
        Set destCell = Worksheets("Stage 3").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        destCell.Value = Ticker.Value
        End If
    ElseIf Cells(1, r).Offset(0, 17) > StockCount Then Next i


End If


Next r
Next i


End Sub

 

[steve the fish]

Not sure i totally inderstand what you are doing but maybe try this:

lastrow = Cells(Rows.Count, "A").End(xlUp).Row

For i = 50 To 59
    For r = 18 To lastrow
        StockCount = Cells(38, i).Value
        If StockCount = 0 Then Exit For
        region = Cells(17, i)
        If Cells(1, r).Offset(0, 4) = region Then
            If Cells(1, r).Offset(0, 17) <= StockCount Then
                Ticker = Cells(1, r).Value
                Set destCell = Worksheets("Stage 3").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
                destCell.Value = Ticker.Value
            Else
                Exit For
            End If
        End If
    Next r
Next i

 

[dnicholsby]

---

Hmm... no errors but my intention didn't work!

In summary i need to copy a ticker from Column A (from row 18 down) into Sheet Stage 3 based on data in columns D (a region) and column Q (a rank).

I have 10 different regions and I have a calculated the number of tickers i need for each region. For example, if the calculated number is 3 then i want all tickers from that region with a ranking of 1, 2 or 3.

The stockcount is meant to determine the number of tickers i need for a region, return the stocks, then move onto the next region.

Any ideas?

 

[Norie]

Does this work?

Sub SelectStocks()
Dim i As Integer
Dim r As Integer
Dim region As String
Dim StockCount As Integer
Dim lastrow As Integer
Dim Ticker As String
Dim destCell As Range

    lastrow = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 50 To 59
    
        For r = 18 To lastrow

            StockCount = Cells(38, i).Value

            If StockCount <> 0 Then

                region = Cells(17, i)

            ElseIf Cells(1, r).Offset(0, 4) = region Then
                If Cells(1, r).Offset(0, 17) <= StockCount Then
                    Ticker = Cells(1, r).Value
                    Set destCell = Worksheets("Stage 3").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
                    destCell.Value = Ticker
                End If
            End If

        Next r

    Next i

End Sub

 

[dnicholsby]

Nope, sadly not.

Why ElseIf? It would be elseif if stockcount = 0 wouldn't it?

 

[dnicholsby]

Got it working. My bad with cell referencing but also I think i was right with the elseif. This is the final code:

Dim i As Integer
Dim r As Integer
Dim region As String
Dim StockCount As Integer
Dim lastrow As Integer
Dim Ticker As String
Dim destCell As range


    lastrow = Cells(Rows.Count, "A").End(xlUp).Row


    For i = 50 To 59
    
        For r = 18 To lastrow


            StockCount = Cells(38, i).Value


            If StockCount <> 0 Then


                region = Cells(17, i).Value


                If Cells(r, 1).Offset(0, 3).Value = region Then
                    If Cells(r, 1).Offset(0, 16).Value <= StockCount Then
                    Ticker = Cells(r, 1).Value
                    Set destCell = Worksheets("Stage 3").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
                    destCell.Value = Ticker
                    End If
                End If
            End If


        Next r


    Next i