Union range vs Arrays (error out of range)

[mokie]

Hello, excel geniuses:)
If anybody could get me a tip I will really glad.


I'm still on beginning of knowledge of it I found probably a "tough nut to crash" in VBA. Starts a new solution I still trying to add something new that I try to develope solution from 58573-copy-rows-if-number-doesn-t-exist-in-all-worksheet'
P45CAL get me a tool:)

If I read about Unions I modificated code and.. I don't get why it's doesn't work. It should be works :) But I've got an error in line
In local window i saw only that still k=0. And I have no idea what I need to change.

tb(j, k + 1) = tb(i, kol(k))
error 9 - out of range.

Below is code and file.

Thank in advanced.



Sub SearchReq5_mokie()
'' 2017-02-11
''First// oryginal SearchReq5 crea18364te by P45Cal on 58573-copy-rows-if-number-doesn-t-exist-in-all-worksheet'
''Current Version: modifacate by Mokie >> loooking for "Union Range" VS Arrays.

Dim tb, OutSht, IsNowhereElse As Boolean, sht


Dim i As Long, j As Long, k As Long, kol
Dim req1, req2, req3DateLowL, req4DateUppL

kol = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)
Set OutSht = ThisWorkbook.ActiveSheet

Dim rng1 As Range
Dim rng2 As Range
Dim arrVar1 As Variant
Dim arrVar2 As Variant
Dim arrNew1 As Variant
Dim arrNew2 As Variant
Dim arrFull() As String
Dim arrFULL_2() As Variant

Dim aaa As Long

Dim arr3 As String
Dim arr4 As String
Dim str As String

Set OutSht = ActiveSheet

With OutSht
Set rng1 = OutSht.Range("M7:M499")
Set rng2 = OutSht.Range("B7:B499")
Set arrVar1 = rng1
Set arrVar2 = rng2
arrNew1 = Application.Transpose(rng1.Value)
arrNew2 = Application.Transpose(rng2.Value)
arr3 = Join(arrNew1, "/")
arr4 = Join(arrNew2, "/")
str = arr3 & "" & arr4
arrFull = Split(str, "/")
arrFULL_2 = Application.Transpose(arrFull)


End With


With ThisWorkbook.Sheets("DataBase")
req1 = .[A2]
req2 = .[B2]
req3DateLowL = .[E2]
req4DateUppL = .[F2]
tb = .Range("A5:L" & .Cells(.Rows.Count, 2).End(xlUp).Row)
For i = 1 To UBound(tb)
If (tb(i, 4) = req1) And (tb(i, 11) >= req3DateLowL) And (tb(i, 11) <= req4DateUppL) Or ((tb(i, 4) = req2 And (tb(i, 11) >= req3DateLowL And tb(i, 11)) <= req4DateUppL)) Then

' If IsError(Application.Match(tb(i, 2), OutSht.Columns(2), 0)) Then

For aaa = 1 To UBound(arrFULL_2)
If IsError(Application.Match(tb(i, 2), arrFULL_2(aaa, 1), 0)) Then
'search the other sheets:

IsNowhereElse = True
For Each sht In ThisWorkbook.Sheets
If sht.Name <> "DataBase" Then


If Not IsError(Application.Match(tb(i, 2), arrFULL_2(aaa, 1), 0)) Then
IsNowhereElse = False
Exit For
End If
End If

Next sht
'end search the other sheets.

If IsNowhereElse Then

j = j + 1
For k = 0 To UBound(kol)
tb(j, k + 1) = tb(i, kol(k))
Next

End If

End If
Next aaa


End If

Next i


If j > 0 Then OutSht.Cells(OutSht.Rows.Count, "A").End(xlUp).Offset(1).Resize(j, UBound(kol) + 1) = tb
End With

End Sub




18364

[Paul_Hossler]

P45cal's good so if anyone can answer it, he can

However in your Dim's



Dim tb, OutSht, IsNowhereElse As Boolean, sht
Dim i As Long, j As Long, k As Long, kol
Dim req1, req2, req3DateLowL, req4DateUppL


anything without a explicit As ..... is a Variant

So tb, OutSht, sht, kol, req1, req2, req3DateLowL, req4DateUppl are all Variants

This means that any kind of variable can be assigned to them and VBA will like it

By explicitly Dim-ing them, VBA can help debug