Switch an array within udf function into a range

[YasserKhalil]

Hello everybody
I have udf function in my attachment .. from which I can extract the gender , the date of birth and governorate according to 14 digits as illustrated in the attachment
I need to do without the array named (MyProvinces) used in the function and use a specific range as in sheets("Data").range("A2:B5")

[romperstomper]

Why use a UDF at all? INDEX and MATCH would be as easy and faster.

[YasserKhalil]

Thanks for reply
I need to know how to convert an array in vba to a range of that array in worksheet

[MrShorty]

This is probably a good place to start: http://www.cpearson.com/excel/ArraysAndRanges.aspx

The complication I see with your MyProvinces array is that currently, each element of the array is a "concatenation" of the ID# & province# text string. If you feel it is necessary to structure the myprovinces array in this way, you will probably need to create the array in a loop

untested pseudocode
For i=1 to number of provinces
myprovinces(i)=i & columnD.cells(i,1).value
next i
rest of code to complete task.

I personally would tend to use a 2D array for something like this. Something where myprovinces(1,1)=01, myprovinces(1,2)="Cairo" and so on, rather than concatenate those two pieces of information together. Not sure it is better, but I find it easier to use each element of the array if there is only one piece of information in each element.

[YasserKhalil]

Thanks Mr. Shorty
I accept cD array , but in fact I'm not good at arrays at all.
Can you do it for me, please?

[MrShorty]

I'm not really a "do it for you" kind of user on this board. I also am probably not the best choice to do it because, like romperstomper, I would probably do it by worksheet formula rather than VBA. But, I observe from your other postings on here that you have a strong preference for VBA solutions rather than worksheet formula solutions, so I guess it is what it is.

I might suggest that there is no time like the present to become more skilled at using arrays. What kinds of things do you not understand? How to dimension arrays? How to load arrays? How to read from the array? Or perhaps you are more uncertain about the different loop structures (because they tend to be important when working with arrays).

I don't know if you reviewed chip pearson's page that I linked to earlier. If needed you might look at a more introductory page like: http://www.classanytime.com/mis333k/sjloopsarrays.html or put "introduction to arrays in VBA" into your favorite internet search engine.