For...Next loop breaks when using Not() operator

103 views Asked by At

I am running a for...next loop that checking whether entries in a dataset meet a certain condition (in this case IsNA). However, changing the if-then-else conditions within this loop to also check whether a condition is not met seems to break the for/next loop. I receive a Next without For error even though that element of the sub hasn't changed.

I'm lost as to why the it thinks there is no next in the for loop when that part of the code hasn't changed.

--Original Working Code--

Option Explicit
Dim i As Double
Dim a As Range
Public ssht As Worksheet
Public susht As Worksheet
Public mdsht As Worksheet
Public LastRow As Long
Dim testcell As Long


Public Sub MissingDataSetCopy()
'Part Bii
'Find rows with NA error
Application.ScreenUpdating = False
Dim i, j As Integer
j = 4
'Finds current range on Summary worksheet
Set ssht = ThisWorkbook.Worksheets("sandbox")
Set mdsht = ThisWorkbook.Worksheets("MissingData")
Set susht = ThisWorkbook.Worksheets("summary")
'Copies data to sandbox sheet as values

susht.UsedRange.copy
ssht.Range("A1").PasteSpecial (xlPasteValues)

LastRow = ssht.Range("A4").CurrentRegion.Rows.Count
Dim testcell As Double
Dim numchk As Boolean
'For...Next look call ISNUMBER test
For i = 860 To 874
     If Application.WorksheetFunction.IsNA(ssht.Range("B" & i)) Then
    mdsht.Range("B" & j & ":H" & j).Value = ssht.Range("A" & i - 1 & ":G" & i - 1).Value
    j = j + 1
    mdsht.Range("B" & j & ":H" & j).Value = ssht.Range("A" & i & ":G" & i).Value
    j = j + 1
    mdsht.Range("B" & j & ":H" & j).Value = ssht.Range("A" & i + 1 & ":G" & i + 1).Value
    j = j + 1
    End If
    Next i

Dim fnd As Variant
Dim rplc As Variant

fnd = "#N/A"
rplc = "=NA()"
mdsht.Cells.Replace what:=fnd, Replacement:=rplc, _
 LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
 SearchFormat:=False, ReplaceFormat:=False

End Sub

--Edit to If Statements--

For i = 860 To 874

    If Application.WorksheetFunction.IsNA(ssht.Range("B" & i)) And Application.WorksheetFunction.IsNA(ssht.Range("B" & i - 1)) Then
        mdsht.Range("B" & j & ":H" & j).Value = ssht.Range("A" & i & ":G" & i).Value
        j = j + 1
    If Application.WorksheetFunction.IsNA(ssht.Range("B" & i)) And Not (Application.WorksheetFunction.IsNA(ssht.Range("B" & i - 1))) Then
        mdsht.Range("B" & j & ":H" & j).Value = ssht.Range("A" & i - 1 & ":G" & i - 1).Value
        j = j + 1
        mdsht.Range("B" & j & ":H" & j).Value = ssht.Range("A" & i & ":G" & i).Value
        j = j + 1
    End If

    Next i
1

There are 1 answers

2
Matteo NNZ On

You need to close the second If block:

For i = 860 To 874

    If Application.WorksheetFunction.IsNA(ssht.Range("B" & i)) And Application.WorksheetFunction.IsNA(ssht.Range("B" & i - 1)) Then
        mdsht.Range("B" & j & ":H" & j).Value = ssht.Range("A" & i & ":G" & i).Value
        j = j + 1
    End If '<-- it was not closed
    If Application.WorksheetFunction.IsNA(ssht.Range("B" & i)) And Not (Application.WorksheetFunction.IsNA(ssht.Range("B" & i - 1))) Then
        mdsht.Range("B" & j & ":H" & j).Value = ssht.Range("A" & i - 1 & ":G" & i - 1).Value
        j = j + 1
        mdsht.Range("B" & j & ":H" & j).Value = ssht.Range("A" & i & ":G" & i).Value
        j = j + 1
    End If

Next i

Or alternatively using the ElseIf keyword if the two conditions (at it seems) are excluding each other:

For i = 860 To 874

    If Application.WorksheetFunction.IsNA(ssht.Range("B" & i)) And Application.WorksheetFunction.IsNA(ssht.Range("B" & i - 1)) Then
        mdsht.Range("B" & j & ":H" & j).Value = ssht.Range("A" & i & ":G" & i).Value
        j = j + 1

    ElseIf Application.WorksheetFunction.IsNA(ssht.Range("B" & i)) And Not (Application.WorksheetFunction.IsNA(ssht.Range("B" & i - 1))) Then
        mdsht.Range("B" & j & ":H" & j).Value = ssht.Range("A" & i - 1 & ":G" & i - 1).Value
        j = j + 1
        mdsht.Range("B" & j & ":H" & j).Value = ssht.Range("A" & i & ":G" & i).Value
        j = j + 1
    End If

Next i