Cannot figure out VBA Error Next without For

Question

I am working on a sub of a larger set of code and when testing this one piece, I get the error Next without For. I have looked, every For has a Next, every If has End If and every While has a Wend.

Can you help me figure this out?

Thanks in advance!

Option Explicit

Sub trueTotal()

Dim ws As Worksheet
Set ws = Worksheets("Sheet1") 'be more explicit than 'ActiveSheet
Dim sPart As String
Dim SearchRange As Range
Dim FindRow As Range
Dim ReturnRowNumber As Integer
Dim currPickTotal As Integer
Dim totalPick As Integer
Dim scanRow As Integer
Dim scanQty As Integer
Dim cell As Range
Dim rng As Range

Set FindRow = Nothing

'Compare the range and loop until equal
While ws.Range("E6:E30") <> ws.Range("F6:F30")
    Set rng = ws.Range("E6:F30")
    For Each cell In rng
        If Not cell Is Empty Then
            ws.Range(rng).Interior.ColorIndex = 4
    Next cell
        While FindRow Is Nothing
            sPart = InputBox("Scan the first part number", "Part Number")  'Get Part Number
            'Set the search range to get the cell row for the part number.
            Set SearchRange = ws.Range("A6", ws.Range("A30").End(xlUp))
            Set FindRow = SearchRange.Find(sPart, LookIn:=xlValues, LookAt:=xlWhole)
            If Not FindRow Is Nothing Then ReturnRowNumber = FindRow.Row
        Wend
        scanRow = ReturnRowNumber
        'Error checking to ensure scanned amount is not greater than required amount
        If totalPick <= ws.Range("E" & scanRow) Then
            scanQty = InputBox("Scan the Quantity", "Quantity")    'Get Quantity
            currPickTotal = ws.Range("F" & scanRow).Value
            totalPick = currPickTotal + scanQty
            Range("F" & scanRow).Value = totalPick
            Set FindRow = Nothing
            If Range("E" & scanRow).Value = Range("F" & scanRow).Value Then
                Range("E" & cell.Row).Interior.ColorIndex = 3
                Range("F" & cell.Row).Interior.ColorIndex = 3
            End If
        Else
            scanQty = InputBox("Scan exceeds the required Quantity, please scan a lower quantity.", "Quantity")    'Get Quantity
            currPickTotal = ws.Range("F" & scanRow).Value
            currPickTotal = currPickTotal - scanQty 'Subtract last scanQty
            totalPick = currPickTotal + scanQty
            Range("F" & scanRow).Value = totalPick
            Set FindRow = Nothing
            If Range("E" & scanRow).Value = Range("F" & scanRow).Value Then
                Range("E" & cell.Row).Interior.ColorIndex = 3
                Range("F" & cell.Row).Interior.ColorIndex = 3
            End If
        End If
Wend
End Sub

You'll also need to loop through your range. While ws.Range("E6:E30") <> ws.Range("F6:F30") won't work. — SJR
– SJR, Commented Aug 8, 2017 at 12:18

user6585761 · Accepted Answer · 2017-08-08 12:08:42Z

2

You're missng End If...

For Each cell In Rng
    If Not cell Is Empty Then
        ws.Range(Rng).Interior.ColorIndex = 4
    End If
Next cell

Hope this helps!

answered Aug 8, 2017 at 12:08

user6585761

Sign up to request clarification or add additional context in comments.

3 Comments

Dana Skyware Over a year ago

Ah, I didn't think I needed one there as I have another If Not statement that did not require End If. Now it says I have a type mismatch on the While ws.Range("E6:E30") <> ws.Range("F6:F30")

B Slater Over a year ago

You can't test 2 arrays/ranges for equality like that. Range("E6:E30") returns a range object full of cells, Excel doesn't know how you want to check whether they're the same. You can define a function that loops through all the cells from E6 to E30 and F6 to F30 to check if they're equal and returns a boolean value then call that function instead of While ws.Range("E6:E30") <> ws.Range("F6:F30")

Dana Skyware Over a year ago

That's what I was trying to accomplish with the While. I need to compare E6 to F6, E7 to F7, and run the loop as long as any of the values down to E30 and F30 are not equal. I will look for a better way to do that. Thanks!

halfer · Accepted Answer · 2018-04-30 11:31:19Z

0

I suggest following;

...
While Not equal_E6_E30_to_F6_F30(ws)
...

Function equal_E6_E30_to_F6_F30(ws As Worksheet)
Dim i As Integer
equal_E6_E30_to_F6_F30 = True
For i = 6 To 30
    If ws.Range("E" + Trim(Str(i))).Value <> ws.Range("F" + Trim(Str(i))).Value Then
        equal_E6_E30_to_F6_F30 = False
        Exit For
    End If
Next
End Function

I've tested it.

edited Apr 30, 2018 at 11:31

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered Aug 8, 2017 at 12:26

Tiefan Ju

5203 silver badges14 bronze badges

Collectives™ on Stack Overflow

Cannot figure out VBA Error Next without For

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related