Compile error and array output

[cortexnotion]

Hi there

I have a problem with "Compile error - next without for". I have tried to insert the AssetArray but I haven't been able to do it correctly.

Also, is there a one liner for the output array rather than having each column as its own line?

Cheers!

Sub mytest()

Dim ArrIN As Variant, ArrOUT As Variant, TechArray As Variant, HeaderArray As Variant, AssetArray As Variant
Dim LR1 As Long, i As Long, o As Long, t As Long, m As Long

LR1 = ThisWorkbook.Sheets("raw").Range("A" & Rows.Count).End(xlUp).Row
ArrIN = ThisWorkbook.Sheets("raw").Range("A2:U" & LR1).Value

HeaderArray = ThisWorkbook.Sheets("raw").Range("A1:U1").Value
AssetArray = Array("sv", "s/v", "sluice", "wo", "w/o", "wash", "hydrant")
TechArray = Array("user1", "user2", "user3")

ReDim ArrOUT(1 To LR1, 1 To 13)
o = 1

For i = LBound(ArrIN) To UBound(ArrIN)

For m = LBound(AssetArray) To UBound(AssetArray)

If (InStr(ArrIN(i, 3), "Type1") > 0 And (InStr(ArrIN(i, 5), "sv") > 0 Or InStr(ArrIN(i, 5), "s/v") > 0 Or InStr(ArrIN(i, 5), "sluice") > 0) And _
(ArrIN(i, 7) & ArrIN(i, 8) & ArrIN(i, 10) <> "") And (ArrIN(i, 7) & ArrIN(i, 8) & ArrIN(i, 10) <> "Unknown")) Or _
(InStr(ArrIN(i, 3), "Type1") > 0 And (InStr(ArrIN(i, 5), "wo") > 0 Or InStr(ArrIN(i, 5), "w/o") > 0 Or InStr(ArrIN(i, 5), "wash") > 0 Or InStr(ArrIN(i, 5), "hydrant") > 0) And _
(ArrIN(i, 7) & ArrIN(i, 10) <> "") And (ArrIN(i, 7) & ArrIN(i, 10) <> "Unknown")) Or _
(InStr(ArrIN(i, 3), "Type1") > 0 And InStr(ArrIN(i, 5), AssetArray(m)) > 0 And ArrIN(i, 7) <> "" And ArrIN(i, 7) <> "Unknown") Then
Next m

For t = LBound(TechArray) To UBound(TechArray)
If InStr(ArrIN(i, 17), TechArray(t)) > 0 Then GoTo NR1
Next t

ArrOUT(o, 1) = ArrIN(i, 1)
ArrOUT(o, 2) = ArrIN(i, 16)
ArrOUT(o, 3) = ArrIN(i, 3)
ArrOUT(o, 4) = ArrIN(i, 4)
ArrOUT(o, 5) = ArrIN(i, 6)
ArrOUT(o, 6) = ArrIN(i, 5)
ArrOUT(o, 7) = ArrIN(i, 11)
ArrOUT(o, 8) = ArrIN(i, 7)
ArrOUT(o, 9) = ArrIN(i, 8)
ArrOUT(o, 10) = ArrIN(i, 10)
ArrOUT(o, 11) = ArrIN(i, 15)
ArrOUT(o, 12) = ArrIN(i, 17)
ArrOUT(o, 13) = ArrIN(i, 18)
o = o + 1
End If

NR1: Next I

With ThisWorkbook.Sheets("Results")
.Range("A1:M1").Value = Application.Index(HeaderArray, 1, Array(1, 16, 3, 4, 6, 5, 11, 7, 8, 10, 15, 17, 18))
.Range("A2:M2").Resize(UBound(ArrOUT)).Value = ArrOUT
End With

End Sub

 

[rlv01]

If you use an If .. End If structure within a For ... Next loop, you must make sure both the If and the End If are within The For ... Next loop . So this is legal:

    For m = LBound(AssetArray) To UBound(AssetArray)
        If (InStr(ArrIN(i, 3), "Type1") > 0) Then
            MsgBox "Success!!!"
        End If
    Next m

    MsgBox "Done"

And this is not:

    For m = LBound(AssetArray) To UBound(AssetArray)
        If (InStr(ArrIN(i, 3), "Type1") > 0) Then
            MsgBox "Success!!!"
        Next m
    End If

    MsgBox "Done"

And is why you are getting the compile error.

 

[cortexnotion]

Thanks @rlv01 I've added in the correct structure I think, but I now get 'End if without block if'. Could you please advise what else is wrong with the code below?

Thanks

ReDim ArrOUT(1 To LR1, 1 To 13)
o = 1

For i = LBound(ArrIN) To UBound(ArrIN)

For m = LBound(AssetArray) To UBound(AssetArray)
If (InStr(ArrIN(i, 3), Type1) > 0 And (InStr(ArrIN(i, 5), "sv") > 0 Or InStr(ArrIN(i, 5), "s/v") > 0 Or InStr(ArrIN(i, 5), "sluice") > 0) And _
(ArrIN(i, 7) & ArrIN(i, 8) & ArrIN(i, 10) <> "") And (ArrIN(i, 7) & ArrIN(i, 8) & ArrIN(i, 10) <> "Unknown")) Or _
(InStr(ArrIN(i, 3), Type1) > 0 And (InStr(ArrIN(i, 5), "wo") > 0 Or InStr(ArrIN(i, 5), "w/o") > 0 Or InStr(ArrIN(i, 5), "wash") > 0 Or InStr(ArrIN(i, 5), "hydrant") > 0) And _
(ArrIN(i, 7) & ArrIN(i, 10) <> "") And (ArrIN(i, 7) & ArrIN(i, 10) <> "Unknown")) Or _
(InStr(ArrIN(i, 3), Type1) > 0 And InStr(ArrIN(i, 5), AssetArray(m)) > 0 And ArrIN(i, 7) <> "" And ArrIN(i, 7) <> "Unknown") Then
End If
Next m

For t = LBound(TechArray) To UBound(TechArray)
If InStr(ArrIN(i, 17), TechArray(t)) > 0 Then GoTo NR1
End If
Next t

ArrOUT(o, 1) = ArrIN(i, 1)
ArrOUT(o, 2) = ArrIN(i, 16)
ArrOUT(o, 3) = ArrIN(i, 3)
ArrOUT(o, 4) = ArrIN(i, 4)
ArrOUT(o, 5) = ArrIN(i, 6)
ArrOUT(o, 6) = ArrIN(i, 5)
ArrOUT(o, 7) = ArrIN(i, 11)
ArrOUT(o, 8) = ArrIN(i, 7)
ArrOUT(o, 9) = ArrIN(i, 8)
ArrOUT(o, 10) = ArrIN(i, 10)
ArrOUT(o, 11) = ArrIN(i, 15)
ArrOUT(o, 12) = ArrIN(i, 17)
ArrOUT(o, 13) = ArrIN(i, 18)
o = o + 1
        End If

 

[DanteAmor]

The logic of your program is not adequate.
You have an If inside the For-Next statement, but you put the End If outside the For-Next statement.
So it sends you that error message.

I really couldn't tell you where the EndIf goes because I don't understand what you want to do in case this huge condition is met and what you want to do in case it doesn't.

The structure should be like this:

For m = LBound(AssetArray) To UBound(AssetArray)
  If huge_condition Then
    'Code if it meets the condition.
  Else
    'Code Does not meet the condition.
  End If
Next m

Could you explain what you need.
Give examples of what you have on the sheet and what you want as result.
Use XL2BB tool, look at my signature.