VBA Paste to first cell or next row

cortexnotion

Board Regular
Joined
Jan 22, 2020
Messages
150
Office Version
  1. 2013
Platform
  1. 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!

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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
I suppose that before putting the second array you must recalculate the last row, then:

VBA Code:
LRNotLoc = ThisWorkbook.Sheets("NotLoc").Range("A" & Rows.Count).End(xlUp).Row + 1
ThisWorkbook.Sheets("NotLoc").Range("A" & LRNotLoc).Resize(UBound(ArrOUT), 10).Value = ArrOUT
End Sub
 
Upvote 0
These lines are not necessary, because you created the "NotLoc" sheet, therefore cell A2 is empty.
By the way, if you are inside the With statement, you are missing a point before Range("A2"):


Rich (BB code):
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top