CODEWITHSUNDEEP
arraysvbaexcel
eeeklavya
eeeklavya

Reputation: 13

Array to store location of matching poistions of 2 different arrays in vba

I have created two arrays both of equal dimensions in VBA (part of code below, used twice to create 2 arrays), I want to use a match function/some other function/ loop to store the matching values location in a new array in vba, and in case of error(non existing value) any appropriate flag(0).

For eg: Array1(USA, UK, Singapore, China) Array2(India, USA, UK, New Zealand) Array3(0,2,3,0)

I am looking how to get to array3{}

thanks in advance!!

Dim wcountryarray() as variant

ReDim wcountryArrayname(1 To maxima5 * Site_countmax * wacountmax) As Variant

counter = 100

lsitetype = 1

For Country_count = 1 To wacountmax

For within_site = 1 To Site_countmax                           

counter = counter + 2           

For Site_types = lsitetype To maxima5 + lsitetype - 1


    wcountryArrayname(Site_types) = WB2.Sheets("Data Input").Cells(Site_types + counter, 2).Value & "|" & _
    WB2.Sheets("Data Input").Cells(Site_types + counter, 3).Value & "|" & _
    WB2.Sheets("Data Input").Cells(Site_types + counter, 4).Value

 Next Site_types

 counter = counter + 30 - maxima5 - (maxima5 - 1)

 columns_Data = columns_Data - 1

 Site_types = Site_types - 1

 lsitetype = Site_types + 1

 Next within_site

 counter = (100 * (Country_count + 1)) - Site_types

 Next Country_count

Upvotes: 0

Views: 42

Answers (1)

Slai
Slai

Reputation: 22866

For example, in VBA this results in Variant(1 To 4) array:

a = [IfError(Match({"India","USA","UK","New Zealand"},{"USA","UK","Singapore","China"},0),)]

Debug.Print Join(a) ' "0 1 2 0"

or with arrays:

array1 = Array("USA", "UK", "Singapore", "China")
array2 = Array("India", "USA", "UK", "New Zealand")
array3 = Evaluate("IFERROR(MATCH({""" & Join(array2, """,""") & """},{""" & Join(array1, """,""") & """},0),)")

Debug.Print Join(array3) ' "0 1 2 0"

Upvotes: 1

Related Questions