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

 

[DanteAmor]

Hi @cortexnotion, You have duplicate the question here

Compile error and array output

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

www.mrexcel.com

The moderator will close one of your questions, I hope it is not this one.
___________________________________________________________

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.

 

[Fluff]

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).