Convert text into certain numbers

[oOpsy]

Hi there,

I'm currently working on a project .I will like to know if it is possibe to actually convert text into certain numbers? An example will be if user select ' system 1', upon clicking the macro button to update sheet2 with the information, it will display '101' in sheet2. Meaning i can define the text to convert into certain numerals.

like system 1 (in sheet 1) will become 200(set by me) in sheet 2 using vba.

Thanks in advance!

 

[PaddyD]

Hi - welcome to the board!

Yes - things like that are possible, though to say anything more specific, you'll need to give us a more detailed example.

 

[oOpsy]

Hi~~

Hope i can explain this properly:

settings.xls is meant for users to select their options. etc column 1 is to select either voltage, current, frequency or resistance. Upon finishing the selection, users should click on the macro button embedded on the sheet to update another wksheet, config.xls.

However instead of display 'voltage' in congfig.xls, i will like to display the assigned code (let say 101) to it. Need help on how to tell excel that voltage = 101, current = 102 etc.


Thanks a million!

 

[PaddyD]

you don't need a macro to do that sort of thing. I'd do it like this:

1) Set up a source table that includes the allowable entries (voltage etc) in one column and their associated valies (101 etc)

2) Set up the data entry using data validation (check it out in the help files) to ensure only legitimate values are entered

3) use a vlookup() formula to return the codes for the entries - again, review excel help file for vlookup(), and search the board for examples of its use, including:

http://www.mrexcel.com/board2/viewtopic.php?t=62709

...post back if yo can't get it sorted - but this time include the details of what ranges your data occupies etc.

an example follows:

Book1
	A	B	C	D	E	F
1	Source	Return		Category	Value
2	Voltage	101		Voltage	101
3	Current	102		Current	102
4	Current	102		Frequency	103
5	Frequency	103		Resistance	104
6	Resistance	104
7	Voltage	101
8
Sheet1

formula is:

=VLOOKUP(A2,$D$2:$E$5,2,0)

 

[oOpsy]

Thanks a lot for the help! Managed to create what i wanted