VBA: Transferring Dynamic Array with data between functions

[Sugarray]

Goodday,

Iv'e discovered VBA a week ago, and have made some progress, but still lack massive amounts of basic knowledge in both Excel and VBA.

Brief overview: I have created a userform with a combobox, from which the user can select a town name, the end result being that the userform will then display a name of an attorney that works in that area. That is the big picture.

But what is relevant, is that i have now created a function in my module1 (getListCloseTown), to it is given the name of the town(townName) selected(i will call it from the useform later, for now i am just testing it by giving it names in the module). This function must then go to a table on the sheet(WC Close Relevant) and scroll down the C column, in which all the town names are found, untill it finds the town. It then, via code i will display just now, gathers that town and various town names right of that name into a dynamic Array (NamesOfCloseTowns). The other town names, are towns that fall within close proximity to the original name. I now have all these names recorded in my dynamic array, as mentioned above.


Below it what I have described so far. I have tested it with the debugger, and it seems to work.
,

Dim NamesOfCloseTowns() As String      

Sub getListCloseTowns(townName As String)
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim l As Integer

    i = 1
    j = 3
    k = 0
    l = 0

    For i = 1 To 100

        If Sheets("WC Close Relevant").Cells(i, 3).Value = townName Then
            While WorksheetFunction.IsText(Sheets("WC Close Relevant").Cells(i, j).Value) 'counts available towns
                j = j + 1
            Wend
            
            ReDim NamesOfCloseTowns(j)
             
            For k = 1 To (j - 3)
                NamesOfCloseTowns(k) = Sheets("WC Close Relevant").Cells(i, k + 2).Value
               
                Next k
            
            Exit For
        End If
    Next i
    
    
End Sub

,

My first problem now arises, I have all the town names, but now need to go to an other sheet(Attorneys). It's D column contains names of town, and it's G column contains names of attorneys. Note that more than one attorney may live in an town, so town names are repeated in the D column. I need a similar thing to happen as above, in the end I need an dynamic array that will contain all the names of all the attorneys from all the relevant town.
[so if my towns are A and E. and in A lives mr.80 and mrs.90, and in D lives mr.33. Then in the end the array must contain; mr.80,mrs.90,mr.33]
I was hoping to use a similar code that i used to find the town names, but then got a very confused.

Problem 1: How do I use the dynamic array populated with data in the first function in the second function?(I figured I should just be able to use it's name, but then how do I know how long it is, for it is not set, and the counter used was defined in the previous function)

Problem 2: The first function was just given a string, to find an array. Now this function i given an array, to find an even bigger array. This also got me. I did some reading and found multiple arrays, I am not sure if those will be practical for this example?

I know I have given a lot of info, hope it makes sence.

Any advice or help that can point me in the right direction will be much appreciated
Thank you

[abousetta]

Hi,

I'm not exactly sure I understand so this is more of a stab in the dark. There are different ways to merge two arrays depending on the number of dimensions. In your Mr.80, Mrs. 90 example this seems to be a 1D array. Therefore you can convert each array to string, concatenate the strings and convert back to a single array in one line of code:

Sub test()

Dim oldArr1, oldArr2, newArr

oldArr1 = Array("Mr.80", "Mrs.90")
oldArr2 = Array("Mr.35", "Mrs.45")

newArr = Split(Join(oldArr1) & " " & Join(oldArr2))

MsgBox Join(newArr, vbCrLf)

End Sub

Hope this helps.

abousetta

[Sugarray]

Thanks for the reply, I will definitely be able to use that towards the end of my project.

I am going to try again this time using attached pictures of the sheets i refer to, an make a hypothetical example.

Overview: I need two functions, the first i have written and tested, it works. It is also attached in my first post.
The function(getListCloseTowns) is given a town (nametownName As String). It then goes to a sheet(WC Close Relevant).

Below i have attached an example of the sheet.

,First Function.jpg,

So the function will search in column C for the town name. Lets use the example of 'PORT ALFRED'. So the function will find the name in the second row, and then place the town now along with all the town names next to it(it could be up to 50) in a dynamic array. Thus the result for this example would yield an array containing "PORT ALFRED, ALEXANDRIA, GRAHAMSTOWN". As i mentioned above, it all seems to work up to this point.

I now need to write a function that will use the above array to look in an other sheet(attorneys).
Below i have attached an example of the sheet.

,Second function.jpg,

It must use the town names from the array to search column D, for the same towns(Note: town names are repeated in the column). And then place the corresponding Attorney's names,from column G, in a new array. So we can continue the 'PORT ALFRED' example. We have from the array "PORT ALFRED, ALEXANDRIA, GRAHAMSTOWN", the function should search and find only 2 'PORT ALFRED' entries(a normal example will have about 8 towns, resulting in about 17 attorney names), and then look in column G. Thus the final result being that a new array is created with "A PARKER & ASSOCIATES" and "BAWA ABRAHAMS & ASSOCIATES" in it.

Hope this clears things up
Thank you for your time

[abousetta]

Can you upload a sample workbook with the same structure as your original. You can replace the real data with dummy data?

Second, enumerate each step and write next to it what the result should be from the sample workbook.

Right now it's just a lot of reading and imaging on my part.

[Sugarray]

thanks again for reply!

I have made a file with the two relevant sheets, and the code is in module one.
I unsure if i did the enumerate thing right. but i commented on all my code and attempted code

I also made notes on the sheets, based on the example of giving the townName of "SOMERSET WEST" to the 1st function.

Note, i wanted the final array to contain names, but just realized there are copies, so changing it to contain the code of each person found in the column next to the name, this should make sense when you see it. (each code is unique, my bad for having wanting to use the firms name)

Really hoping that it will make sense this time,

thank you very much for taking time to understand

Sugarray.xlsm