Creating a 3rd Array from 2 different sized Arrays

Question

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.

Your macro is trying to fill an array based on EASR_SS_VI_All if VI_VRR4 doesn't have the value it will print N/A, is that your goal? — Damian
– Damian, Commented Sep 16, 2019 at 14:47

SJR · Accepted Answer · 2019-09-16 14:54:51Z

2

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

answered Sep 16, 2019 at 14:54

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

plungeintome Over a year ago

if I want this in column form do I transpose or can I do Resize(i, )?

SJR Over a year ago

Range("A1").Resize(i) = application.transpose(C).

Scott Craner · Accepted Answer · 2019-09-16 15:21:18Z

2

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

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

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

edited Sep 16, 2019 at 15:21

answered Sep 16, 2019 at 14:49

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

9 Comments

plungeintome Over a year ago

This loop won't work as i is undefined until inside the loop. Also is Exit Loop a command within VBA? I have never seen it used before.

Scott Craner Over a year ago

@plungeintome see edit, i flipped the loops and yes Exit Loop is a vba command

Scott Craner Over a year ago

@plungeintome no Exit Loop does not exist. see edit. Should be Exit For

plungeintome Over a year ago

for some reason this is still giving me all N/A's. Even with the Exit For

plungeintome Over a year ago

I will attempt to post the arrays that are being compared and what I should get. There are over 1500 inputs in the array. will only do first 10.

|

Collectives™ on Stack Overflow

Creating a 3rd Array from 2 different sized Arrays

2 Answers 2

2 Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related