VBA Take specific values from one array into another array dynamically

Question

I have an array with over 130 elements, out of those elements I only want specific elements. Those elements I have been able to specify with an if statement and an Instr function. However, I am stuck when figuring out how to those values from main array into a different array. The issue is that I don't know how many values will actually fit the requirements and so the second array I can't define before the values are counted.

How would I transfer certain values that fit the Instr function from one array to the other array.

Current code -

For v = 0 To UBound(NViews)

  ViewNames = NViews(v)
    If InStr(ViewNames, "Triage Folder") Then
    ReDim TriageNames(0 To p)
    TriageNames(p) = NViews(v)
    p = p + 1
  End If

Next

Updated Code**

p = 1
ReDim TriageNames(0 To p)

For v = 0 To UBound(NViews)

ViewNames = NViews(v)
If InStr(ViewNames, "Triage Folder") Then

TriageNames(p) = NViews(v)
p = p + 1

ReDim Preserve TriageNames(0 To p)

End If

Next

Use ReDim Preserve on the second array, after you've populated it. — BigBen
– BigBen, Commented Oct 6, 2022 at 15:32
@BigBen I did it, it works, I'm not sure why and both the first and the last element are now blank. — Drawleeh
– Drawleeh, Commented Oct 6, 2022 at 15:45
A better solution is to use a an ArrayList. Create the arraylist using create object (e.g. CreateObject("ArrayList")_ Then just use the .add method to add items to the arraylist. W hen you have processed the first array, you can get an array of the arraylist by using its .ToArray method. — freeflow
– freeflow, Commented Oct 6, 2022 at 17:42

BigBen · Accepted Answer · 2022-10-06 15:53:21Z

1

Make TriageNames the same size as NViews, then use ReDim Preserve, after you populated TriageNames completely:

Dim TriageNames As Variant
ReDim TriageNames(0 to Ubound(NViews))

For v = 0 To Ubound(NViews)
   If InStr(NViews(v), "Triage Folder") > 0 Then
       Dim p As Long
       TriageNames(p) = NViews(v)
       p = p + 1
   End If
Next

If p > 0 Then
   ReDim Preserve TriageNames(0 to p - 1)
End If

answered Oct 6, 2022 at 15:53

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

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

Collectives™ on Stack Overflow

VBA Take specific values from one array into another array dynamically

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related