Function for Regions

[Roderick_E]

I'm trying to create a UD function that returns the region of the world of a two-letter country code but there must be a limit on strings. See my rlist Any ideas how to go about this differently? or can you join two strings?

Public Function getregion(ctry As String)
Application.Volatile
Dim clist As String
Dim rlist As String
clist = "AF,AX,AL,DZ,AS,AD,AO,AI,AQ,AG,AR,AM,AW,AU,AT,AZ,BS,BH,BD,BB,BY,BE,BZ,BJ,BM,BT,BO,BQ,BA,BW,BV,BR,IO,BN,BG,BF,BI,KH,CM,CA,CV,KY,CF,TD,CL,CN,CX,CC,CO,KM,CG,CD,CK,CR,CI,HR,CU,CW,CY,CZ,DK,DJ,DM,DO,EC,EG,SV,GQ,ER,EE,ET,FK,FO,FJ,FI,FR,GF,PF,TF,GA,GM,GE,DE,GH,GI,GR,GL,GD,GP,GU,GT,GG,GN,GW,GY,HT,HM,VA,HN,HK,HU,IS,IN,ID,IR,IQ,IE,IM,IL,IT,JM,JP,JE,JO,KZ,KE,KI,KP,KR,KW,KG,LA,LV,LB,LS,LR,LY,LI,LT,LU,MO,MK,MG,MW,MY,MV,ML,MT,MH,MQ,MR,MU,YT,MX,FM,MD,MC,MN,ME,MS,MA,MZ,MM,NA,NR,NP,NL,NC,NZ,NI,NE,NG,NU,NF,MP,NO,OM,PK,PW,PS,PA,PG,PY,PE,PH,PN,PL,PT,PR,QA,RE,RO,RU,RW,BL,SH,KN,LC,MF,PM,VC,WS,SM,ST,SA,SN,RS,SC,SL,SG,SX,SK,SI,SB,SO,ZA,GS,SS,ES,LK,SD,SR,SJ,SZ,SE,CH,SY,TW,TJ,TZ,TH,TL,TG,TK,TO,TT,TN,TR,TM,TC,TV,UG,UA,AE,GB,US,UM,UY,UZ,VU,VE,VN,VG,VI,WF,EH,YE,ZM,ZW"
rlist = "Asia,Europe,Europe,Africa,Oceania,Europe,Africa,Caribbean,Antarctica,Caribbean,South America,Asia,Caribbean,Oceania,Europe,Asia,Caribbean,Asia,Asia,Caribbean,Europe,Europe,Central America,Africa,Northern America,Asia,South America,Caribbean,Europe,Africa,Other,South America,Other,Asia,Europe,Africa,Africa,Asia,Africa,Northern America,Africa,Caribbean,Africa,Africa,South America,Asia,Other,Other,South America,Africa,Africa,Africa,Oceania,Central America,Africa,Europe,Caribbean,Caribbean,Asia,Europe,Europe,Africa,Caribbean,Caribbean,South America,Africa,Central America,Africa,Africa,Europe,Africa,South America,Europe,Oceania,Europe,Europe,South America,Oceania,Other,Africa,Africa,Asia,Europe,Africa,Europe,Europe,Northern America,Caribbean,Caribbean,Oceania,Central America,Europe,Africa,Africa,South America,Caribbean,Other,Europe,Central America,Asia,Europe,Europe,Asia,Asia,Asia,Asia,Europe,Europe,Asia,Europe,Caribbean,Asia,Europe,Asia,Asia,Africa,Oceania,Asia,Asia,Asia,Asia,Asia,Europe,Asia,Africa,Afr
ica,Africa,Europe,Europe,Europe,Asia,Europe,Africa,Africa,Asia,Asia,Africa,Europe,Oceania,Caribbean,Africa,Africa,Africa,Central America,Oceania,Europe,Europe,Asia,Europe,Caribbean,Africa,Africa,Asia,Africa,Oceania,Asia,Europe,Oceania,Oceania,Central America,Africa,Africa,Oceania,Oceania,Oceania,Europe,Asia,Asia,Oceania,Asia,Central America,Oceania,South America,South America,Asia,Oceania,Europe,Europe,Caribbean,Asia,Africa,Europe,Europe,Africa,Caribbean,Africa,Caribbean,Caribbean,Caribbean,Northern America,Caribbean,Oceania,Europe,Africa,Asia,Africa,Europe,Africa,Africa,Asia,Caribbean,Europe,Europe,Oceania,Africa,Africa,,Africa,Europe,Asia,Africa,South America,Europe,Africa,Europe,Europe,Asia,Asia,Asia,Africa,Asia,Asia,Africa,Oceania,Oceania,Caribbean,Africa,Asia,Asia,Caribbean,Oceania,Africa,Europe,Asia,Europe,Northern America,Other,South America,Asia,Oceania,South America,Asia,Caribbean,Caribbean,Oceania,Africa,Asia,Africa,Africa"
clunbound = Split(clist, ",")
rlunbound = Split(rlist, ",")
For i = LBound(clunbound) To UBound(clunbound)
If ctry = clunbound(i) Then
rgn = rlunbound(i)
End If
Next i
getregion = rgn
End Function

 

[Fluff]

You can join them

rlist = "Asia,Europe,Europe,Africa,Oceania,Europe,Africa,Caribbean,Antarctica,Caribbean,South America,Asia,Caribbean,Oceania,Europe,Asia,Caribbean,Asia,Asia,Caribbean,Europe,Europe,Central America,Africa,Northern America,Asia,South America,Caribbean,Europe,Africa,Other,South America,Other,Asia,Europe,Africa,Africa,Asia,Africa,Northern America,Africa,Caribbean,Africa,Africa,South America,Asia,Other,Other,South America,Africa,Africa,Africa,Oceania,Central America,Africa,Europe,Caribbean,Caribbean,Asia,Europe,Europe,Africa,Caribbean,Caribbean,South America,Africa,Central America,Africa,Africa,Europe,Africa,South America,Europe,Oceania,Europe,Europe,South America,Oceania,Other,Africa,Africa,Asia,Europe,Africa,Europe,Europe,Northern America,Caribbean,Caribbean,Oceania,Central America,Europe,Africa,Africa,South America,Caribbean,Other,Europe,Central America,Asia,Europe,Europe,Asia,Asia,Asia,Asia,Europe,Europe,Asia,Europe,Caribbean,Asia,Europe,Asia,Asia,Africa,Oceania,Asia,Asia,Asia,Asia,Asia,Europe,Asia"
rlist2 = "Africa,Africa,Africa,Europe,Europe,Europe,Asia,Europe,Africa,Africa,Asia,Asia,Africa,Europe,Oceania,Caribbean,Africa,Africa,Africa,Central America,Oceania,Europe,Europe,Asia,Europe,Caribbean,Africa,Africa,Asia,Africa,Oceania,Asia,Europe,Oceania,Oceania,Central America,Africa,Africa,Oceania,Oceania,Oceania,Europe,Asia,Asia,Oceania,Asia,Central America,Oceania,South America,South America,Asia,Oceania,Europe,Europe,Caribbean,Asia,Africa,Europe,Europe,Africa,Caribbean,Africa,Caribbean,Caribbean,Caribbean,Northern America,Caribbean,Oceania,Europe,Africa,Asia,Africa,Europe,Africa,Africa,Asia,Caribbean,Europe,Europe,Oceania,Africa,Africa,,Africa,Europe,Asia,Africa,South America,Europe,Africa,Europe,Europe,Asia,Asia,Asia,Africa,Asia,Asia,Africa,Oceania,Oceania,Caribbean,Africa,Asia,Asia,Caribbean,Oceania,Africa,Europe,Asia,Europe,Northern America,Other,South America,Asia,Oceania,South America,Asia,Caribbean,Caribbean,Oceania,Africa,Asia,Africa,Africa"
rlist3 = rlist & rlist2

 

[shg]

I'd put them on a worksheet, which you could hide it desired.

 

[Roderick_E]

Thanks shg, I really need it in a function so I can utilize it on multiple spreadsheets. I came to the same conclusion as Fluff! Thanks all:

SOLUTION:

Public Function getregion(ctry As String)
Application.Volatile
Dim clist As String
Dim rlist1 As String
Dim rlist2 As String
Dim rlist3 As String
Dim rlist4 As String
clist = "AF,AX,AL,DZ,AS,AD,AO,AI,AQ,AG,AR,AM,AW,AU,AT,AZ,BS,BH,BD,BB,BY,BE,BZ,BJ,BM,BT,BO,BQ,BA,BW,BV,BR,IO,BN,BG,BF,BI,KH,CM,CA,CV,KY,CF,TD,CL,CN,CX,CC,CO,KM,CG,CD,CK,CR,CI,HR,CU,CW,CY,CZ,DK,DJ,DM,DO,EC,EG,SV,GQ,ER,EE,ET,FK,FO,FJ,FI,FR,GF,PF,TF,GA,GM,GE,DE,GH,GI,GR,GL,GD,GP,GU,GT,GG,GN,GW,GY,HT,HM,VA,HN,HK,HU,IS,IN,ID,IR,IQ,IE,IM,IL,IT,JM,JP,JE,JO,KZ,KE,KI,KP,KR,KW,KG,LA,LV,LB,LS,LR,LY,LI,LT,LU,MO,MK,MG,MW,MY,MV,ML,MT,MH,MQ,MR,MU,YT,MX,FM,MD,MC,MN,ME,MS,MA,MZ,MM,NA,NR,NP,NL,NC,NZ,NI,NE,NG,NU,NF,MP,NO,OM,PK,PW,PS,PA,PG,PY,PE,PH,PN,PL,PT,PR,QA,RE,RO,RU,RW,BL,SH,KN,LC,MF,PM,VC,WS,SM,ST,SA,SN,RS,SC,SL,SG,SX,SK,SI,SB,SO,ZA,GS,SS,ES,LK,SD,SR,SJ,SZ,SE,CH,SY,TW,TJ,TZ,TH,TL,TG,TK,TO,TT,TN,TR,TM,TC,TV,UG,UA,AE,GB,US,UM,UY,UZ,VU,VE,VN,VG,VI,WF,EH,YE,ZM,ZW"
rlist1 = "Asia,Europe,Europe,Africa,Oceania,Europe,Africa,Caribbean,Antarctica,Caribbean,South America,Asia,Caribbean,Oceania,Europe,Asia,Caribbean,Asia,Asia,Caribbean,Europe,Europe,Central America,Africa,Northern America,Asia,South America,Caribbean,Europe,Africa,Other,South America,Other,Asia,Europe,Africa,Africa,Asia,Africa,Northern America,Africa,Caribbean,Africa,Africa,South America,Asia,Other,Other,South America,Africa,Africa,Africa,Oceania,Central America,Africa,Europe,Caribbean,Caribbean,Asia,Europe,Europe,Africa,Caribbean,Caribbean,South America,Africa,Central America,Africa,Africa,Europe,Africa,South America,Europe,Oceania,Europe,Europe,South America,Oceania,Other,Africa,Africa,Asia,Europe,Africa,Europe,Europe,Northern America,Caribbean,Caribbean,Oceania,Central America,Europe,Africa,Africa,South America,Caribbean,Other,Europe,Central America,Asia,Europe,Europe,Asia,Asia,Asia,Asia,Europe,Europe,Asia,Europe,Caribbean,Asia"
rlist2 = "Europe,Asia,Asia,Africa,Oceania,Asia,Asia,Asia,Asia,Asia,Europe,Asia,Africa,Africa,Africa"
rlist3 = "Europe,Europe,Europe,Asia,Europe,Africa,Africa,Asia,Asia,Africa,Europe,Oceania,Caribbean,Africa,Africa,Africa,North America,Oceania,Europe,Europe,Asia,Europe,Caribbean,Africa,Africa,Asia,Africa,Oceania,Asia,Europe,Oceania,Oceania,Central America,Africa,Africa,Oceania,Oceania,Oceania,Europe,Asia,Asia,Oceania,Asia,Central America,Oceania,South America,South America,Asia,Oceania,Europe,Europe,Caribbean,Asia,Africa,Europe,Europe,Africa,Caribbean,Africa,Caribbean,Caribbean,Caribbean,Northern America,Caribbean,Oceania,Europe,Africa,Asia,Africa,Europe,Africa,Africa,Asia,Caribbean,Europe,Europe,Oceania,Africa,Africa,,Africa,Europe,Asia,Africa,South America,Europe,Africa,Europe,Europe,Asia,Asia,Asia,Africa,Asia,Asia,Africa,Oceania,Oceania,Caribbean,Africa,Asia,Asia,Caribbean,Oceania,Africa,Europe,Asia,Europe,Northern America,Other,South America,Asia,Oceania,South America,Asia,Caribbean,Caribbean,Oceania,Africa,Asia,Africa,Africa"
rlist4 = rlist1 & "," & rlist2 & "," & rlist3
clunbound = Split(clist, ",")
rlunbound = Split(rlist4, ",")
For i = LBound(clunbound) To UBound(clunbound)
If ctry = clunbound(i) Then
rgn = rlunbound(i)
End If
Next i
getregion = rgn
End Function

 

[Fluff]

Glad we could help & thanks for the feedback