Array questions VBA (EDUCATIONAL)

[Masha92]

Hi ALL,

Hope you are doing well!

I am new to VBA(started beginning of January) but i made great progress (in my opinion). I have a couple of questions that will help me greatly in my career.

a) I have two dynamic array (1D) A, B. I need to know what is in array A and not in B, the result would be added to array C. And what is not in Array A but it is in Array B, the result would be added to array D.

b) I have a 2D array. How to vlookup inside the 2d Array?

d) How to test if a certain value is in a 1D array?

c) I have duplicated values in a 1D array, how to remove the duplicate ones?

Please note that, i need the solution/code to address exactly what is needed. Ie no collections or any other solution that would do the same results. I need to understand and manipulate arrays. And no other pages, i just need the codes with explanation.

If you can add explanatory comments, i would really appreciate it.


Many thanks to you all! I wish one day i will be like you and help others!
Masha

 

[mikerickson]

re D:
If the array is of strings or number, you can use MATCH to find if an element is in an array

If IsNumeric(Application.Match(someElement, myArray,0)) Then
    MsgBox "Its there !"
Else
    MsgBox "not"
End If

Note that if the term isn't there, Application.Match will return an error value that can be tested. Application.WorksheetFunction.Match will cause a VB error that will crash the routine.

re C:
To remove duplicates from an Array. (Actually to create a new array without duplicate, after creation, that new array could be assigned to the old variable.)

Dim arrNoDuplicates as Variant, Pointer as Long

Redim arrNoDuplicates(LBound(rawArray) to UBound(rawArray))

Pointer = LBound(rawArray) - 1

For i = LBound(rawArray) to UBound(rawArray)
    If IsError(Application.Match(rawArray(i), arrNoDuplicates, 0) Then
        Pointer = Pointer + 1
        arrNoDuplicates(Pointer) = rawArray(i)
    End If
Next i

ReDim Preserve arrNoDuplicates(LBound(rawArray) to Pointer)

 

[Masha92]

Thank you so much for your reply.

For C:

What is Rawarray? I am a bit confused. You didnt declare this variable.

Another thing, is it possible to do it without using Excel functions? I.e Application.worksheetfunction...etc

Many thanks!
Masha

 

[Fluff]

You can filter a 1d array like

Sub Masha92()
   Dim Ary1 As Variant, Ary2 As Variant, Ary3 As Variant
   Dim i As Long
   
   Ary1 = Array("John", "Paul", "George", "Ringo")
   Ary2 = Array("Peter", "Paul", "Mary")
   Ary3 = Ary2
   For i = 0 To UBound(Ary1)
      Ary3 = Filter(Ary3, Ary1(i), False, vbTextCompare)
   Next i
End Sub

But this will filter on partial matches, rather than an exact match

 

[mikerickson]

Thank you so much for your reply.

For C:

What is Rawarray? I am a bit confused. You didnt declare this variable.

Another thing, is it possible to do it without using Excel functions? I.e Application.worksheetfunction...etc

Many thanks!
Masha

rawArray is a variable that I created to represent the mixed array that the remove-duplicates routine works on.

As far as avoiding Excel functions, why? you are working in Excel VBA and those functions are as "legit" (and perhaps faster) than the other VBA functions or routines.

 

[Masha92]

Hi Fluff,

Many thanks but i need exact matches unfortunately.

 

[Masha92]

Because I am a bit good with excel functions. I need to practice more on VBA functions and techniques. So i am aware of worksheetfunction.match...etc

But thanks anyway

 

[Masha92]

Any help with the other points?