Help Please on Select-case auto add new info

[Closed]
Report
-
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
-
Hello, Could you please help me?
I have very little-too no knowledge with excels macros, and I have no clue how to solve the next issue:
I have this macro:
' Converts a number into a city
Function GetCiudad(Digit)
Select Case Val(Digit)
Case 1: GetCiudad = "Santiago"
Case 2: GetCiudad = "Concepcion"
Case 3: GetCiudad = ""
Case Else: GetCiudad ""
End Select
End Function


Other than that, in excell on sheet 1 I have numbers generated randomly from 1 to 2 which are later substituted by a city from sheet 2 using: =GetCiudad(ENTERO(ALEATORIO()*2)+1) (in english i think it would be: =getciudad(whole(random()*2)+1) .

On sheet 2 it looks like this:
A1 A2
1 santiago
2 concepcion



I wanted to know how, if on sheet 2 I add numbers and cities:
A1 A2
1 santiago
2 concepcion
3 New york
4 Paris
, and on sheet one I change the random number generator range (for example to produce numbers from 1 to 4)

"How can I automatically get the macros to update this?" as in automatically adding case3: getciudad= "new york"
case4: getciudad= "paris"


Or maybe there is even an easier way to subsitute the random numbers for their corresponding city? I am making the random numbers using: =(ENTERO(ALEATORIO()*2)+1)



Thanks for any help you could give me!





1 reply

Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Laura,

I have no experience with custom function, but wouldn't this work?:

Case 1: GetCiudad = Sheets("sheet2").Range("A1").Value
Case 2: GetCiudad = Sheets("sheet2").Range("A2").Value
Case 3: GetCiudad = Sheets("sheet2").Range("A3").Value
Case 4: GetCiudad = Sheets("sheet2").Range("A4").Value
Case 5: GetCiudad = ""
Case Else: GetCiudad ""

Best regards,
Trowa