CODEWITHSUNDEEP
excelvba
plungeintome
plungeintome

Reputation: 31

Creating a 3rd Array from 2 different sized Arrays

I am trying to create a 3rd array from Array A and Array B, where Array B is contained within Array A. To keep it short I will just post the loop here, The arrays that I want are correct, I have already printed them and confirmed they are correct. It is just going somewhere wrong in my loop.

Array A: [a1, a2, a3, b1, b2, b3, c1, c2, c3] 
Array B: [a1, a2, a3, b1, b3, c1, c2] 
Array C (what I want): [a1, a2, a3, b1, N/A, b3, c1, c2, N/A]

I think my error is coming from where my Next i is placed. I cannot put it inside the If statement but I am not sure where it should go.

For j = 1 To UBound(EASR_VI_New)
    For i = 1 To UBound(VI_VRR4)
        If VI_VRR4(i, 1) = EASR_SS_VI_All(j, 1) Then
            TE_VRR4E(i, 1) = TE_VRR4F(i, 1)
        ElseIf VI_VRR4(i, 1) <> EASR_SS_VI_All(j, 1) Then
            TE_VRR4E(i, 1) = "N/A"
        End If
    Next i
Next j

Currently the Array TE_VRR4E is printing all N/A, however when I print VI_VRR4 and EASR_SS_VI_All the first 3 values in each array are the exact same. So I don't know why it is giving me all N/A.

Upvotes: 0

Views: 64

Answers (2)

Scott Craner
Scott Craner

Reputation: 152505

It is putting all N/A because it is continuing to loop and check even after finding the match. The next loop then covers up the correct return with N/A because the next value in Array B does not equal the value in Array A being tested.

Default the N/A and exit the loop if positive.

For i = 1 To UBound(VI_VRR4)
    TE_VRR4E(i, 1) = "N/A"
    For j = 1 To UBound(EASR_VI_New)        
        If VI_VRR4(i, 1) = EASR_SS_VI_All(j, 1) Then
            TE_VRR4E(i, 1) = TE_VRR4F(i, 1)
            Exit For
        End If
    Next j
Next i

To show it works:

Dim A As Variant, B As Variant, C As Variant, i As Long, j as Long

A = ActiveSheet.Range("A1:A9").Value
B = ActiveSheet.Range("B1:B7").Value

ReDim C(1 To UBound(A, 1), 1 To 1)

For i = 1 To UBound(A, 1)
    C(i, 1) = "N/A"
    For j = 1 To UBound(B, 1)
        If A(i, 1) = B(j, 1) Then
            C(i, 1) = A(i, 1)
            Exit For
        End If
    Next j
Next i

ActiveSheet.Range("D1").Resize(UBound(A, 1), 1).Value = C

enter image description here

BTW: this same output can be done with a simple formula:

=IFERROR(VLOOKUP(A1,B:B,1,FALSE),"N/A")

enter image description here

Upvotes: 2

SJR
SJR

Reputation: 23081

Scott has diagnosed your problem.

I post this just because it's a way of avoiding 2 loops.

Sub x()

Dim A As Variant, B As Variant, C() As String, i As Long

A = Array("a1", "a2", "a3", "b1", "b2", "b3", "c1", "c2", "c3")
B = Array("a1", "a2", "a3", "b1", "b3", "c1", "c2")

ReDim C(UBound(A))
For i = LBound(A) To UBound(A)
    If IsNumeric(Application.Match(A(i), B, 0)) Then
        C(i) = A(i)
    Else
        C(i) = "N/A"
    End If
Next i

Range("A1").Resize(, i) = C

End Sub

enter image description here

Upvotes: 2

Related Questions