cortexnotion
Board Regular
- Joined
- Jan 22, 2020
- Messages
- 150
- Office Version
- 2013
- Platform
- Windows
Hi All
I'm building a series of data matches and I'm stuck with the outputs of my array. The first array posts to Sheet "NotLoc" cell A2. The second array needs to paste to 2nd empty row, or A2 if the first array had not data returned. Each array works well on its own but combined I get no results so I'm my 'LRNotLoc' if section maybe be wrong?
Any guidance appreciated!
I'm building a series of data matches and I'm stuck with the outputs of my array. The first array posts to Sheet "NotLoc" cell A2. The second array needs to paste to 2nd empty row, or A2 if the first array had not data returned. Each array works well on its own but combined I get no results so I'm my 'LRNotLoc' if section maybe be wrong?
Any guidance appreciated!
VBA Code:
Dim ArrIN As Variant, ArrOUT As Variant
Dim AVArray As Variant, SVArray As Variant, WOArray As Variant
Dim LR As Long, i As Long, o As Long, a As Long
Dim Type1 As String, Type2 As String, Type3 As String, Type4 As String, Type5 As String, Type6 As String
LR = ThisWorkbook.Sheets("Update Required_2").Range("A" & Rows.Count).End(xlUp).Row
ArrIN = ThisWorkbook.Sheets("Update Required_2").Range("A2:U" & LR).Value
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "NotLoc"
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "NonOp"
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Attr"
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Miss"
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "LocChg"
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Completed"
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Poor"
Type4 = "Missing on map"
Type2 = "Attribute Only"
Type3 = "Location Change"
Type1 = "Not Located"
Type5 = "Not Operational"
Type6 = ""
AVArray = Array("Air", "AV", "A/V")
SVArray = Array("Sluice", "SV", "S/V", "Line")
WOArray = Array("Wash", "WO", "W/O")
'NOT LOCATED TAB
Dim LRNotLoc As Long
With ThisWorkbook.Sheets("NotLoc")
If IsEmpty(Range("A2")) Then
LRNotLoc = 2
Else
LRNotLoc = ThisWorkbook.Sheets("NotLoc").Range("A" & Rows.Count).End(xlUp).Row.Offset(2, 0)
End If
End With
'NOT LOCATED: V
On Error Resume Next
ThisWorkbook.Sheets("Update Required_2").ShowAllData
On Error GoTo 0
ReDim ArrOUT(1 To LR, 1 To 10)
o = 1
For i = LBound(ArrIN) To UBound(ArrIN)
'If GISST updated = No
If ArrIN(i, 12) = "No" Then
'If Update Type is Not Located
If ArrIN(i, 3) = Type1 Then
'If UID, Operational Status, Open Status and Close Direction are not empty and not unknown
If Not IsEmpty(ArrIN(i, 6)) And _
Not IsEmpty(ArrIN(i, 7)) And _
Not IsEmpty(ArrIN(i, 8)) And _
Not IsEmpty(ArrIN(i, 10)) And _
InStr(ArrIN(i, 6), "Unknown") = 0 And _
InStr(ArrIN(i, 7), "Unknown") = 0 And _
InStr(ArrIN(i, 8), "Unknown") = 0 And _
InStr(ArrIN(i, 10), "Unknown") = 0 Then
'If Asset Type is Air Valve
For a = LBound(AVArray) To UBound(AVArray)
If InStr(ArrIN(i, 5), AVArray(a)) > 0 Or InStr(ArrIN(i, 11), AVArray(a)) > 0 Then
ArrOUT(o, 1) = ArrIN(i, 1)
ArrOUT(o, 2) = ArrIN(i, 2)
ArrOUT(o, 3) = ArrIN(i, 3)
ArrOUT(o, 4) = ArrIN(i, 4)
ArrOUT(o, 5) = ArrIN(i, 5)
ArrOUT(o, 6) = ArrIN(i, 11)
ArrOUT(o, 7) = ArrIN(i, 6)
ArrOUT(o, 8) = ArrIN(i, 7)
ArrOUT(o, 9) = ArrIN(i, 8)
ArrOUT(o, 9) = ArrIN(i, 10)
o = o + 1
End If
Next a
End If
End If
End If
Next i
ThisWorkbook.Sheets("NotLoc").Range("A2:J2").Resize(UBound(ArrOUT)).Value = ArrOUT
'NOT LOCATED: SV
Dim i1 As Long, o1 As Long, a1 As Long
On Error Resume Next
ThisWorkbook.Sheets("Update Required_2").ShowAllData
On Error GoTo 0
ReDim ArrOUT(1 To LR, 1 To 10)
o1 = 1
For i1 = LBound(ArrIN) To UBound(ArrIN)
'If GISST updated = No
If ArrIN(i1, 12) = "No" Then
'If Update Type is Not Located
If ArrIN(i1, 3) = Type1 Then
'If UID, Operational Status, Open Status and Close Direction are not empty and not unknown
If Not IsEmpty(ArrIN(i1, 6)) And _
Not IsEmpty(ArrIN(i1, 7)) And _
Not IsEmpty(ArrIN(i1, 8)) And _
Not IsEmpty(ArrIN(i1, 10)) And _
InStr(ArrIN(i1, 6), "Unknown") = 0 And _
InStr(ArrIN(i1, 7), "Unknown") = 0 And _
InStr(ArrIN(i1, 8), "Unknown") = 0 And _
InStr(ArrIN(i1, 10), "Unknown") = 0 Then
'If Asset Type is Air Valve
For a1 = LBound(SVArray) To UBound(SVArray)
If InStr(ArrIN(i1, 5), SVArray(a1)) > 0 Or InStr(ArrIN(i1, 11), SVArray(a1)) > 0 Then
ArrOUT(o1, 1) = ArrIN(i1, 1)
ArrOUT(o1, 2) = ArrIN(i1, 2)
ArrOUT(o1, 3) = ArrIN(i1, 3)
ArrOUT(o1, 4) = ArrIN(i1, 4)
ArrOUT(o1, 5) = ArrIN(i1, 5)
ArrOUT(o1, 6) = ArrIN(i1, 11)
ArrOUT(o1, 7) = ArrIN(i1, 6)
ArrOUT(o1, 8) = ArrIN(i1, 7)
ArrOUT(o1, 9) = ArrIN(i1, 8)
ArrOUT(o1, 9) = ArrIN(i1, 10)
o1 = o1 + 1
End If
Next a1
End If
End If
End If
Next i1
ThisWorkbook.Sheets("NotLoc").Range("A" & LRNotLoc).Resize(UBound(ArrOUT), 10).Value = ArrOUT
End Sub