Much help needed on conversion!

[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!

[Bob Phillips]

That just sounds that you need to define a lookup table. Check VLOOKUP in
Help.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"oOpsy" <oOpsy.1z6hva_1133133602.4242@excelforum-nospam.com> wrote in
message news:oOpsy.1z6hva_1133133602.4242@excelforum-nospam.com...
>
> 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!
>
>
> --
> oOpsy
> ------------------------------------------------------------------------
> oOpsy's Profile:
http://www.excelforum.com/member.php...o&userid=29132
> View this thread: http://www.excelforum.com/showthread...hreadid=488525
>

[oOpsy]

Thanks for the reply!

but vlookup is more of like a search function. I need something which is like to decode let say this term 'voltage' into a numeral let's say '101'

[Kaak]

Function GetNumberFromString(InputString)

Dim InputString as string
Dim OutputNumber as integer

Select Case InputString

Case "String 1"

OutputNumber = 101

Case "String 2"

OutputNumber = 289

Case "String 3"

OutputNumber = 753

End Select

GetNumberFromString = OutputNumber

End Function

[Kaak]

Sorry,

remove Dim InputSting as string

[Bob Phillips]

Do you mean that when you enter voltage, you want that cell translated?
Wouldn't you want to keep the original input, and have the translation
ancillary to that data?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"oOpsy" <oOpsy.1z712y_1133158502.6156@excelforum-nospam.com> wrote in
message news:oOpsy.1z712y_1133158502.6156@excelforum-nospam.com...
>
> Thanks for the reply!
>
> but vlookup is more of like a search function. I need something which
> is like to decode let say this term 'voltage' into a numeral let's say
> '101'
>
>
> --
> oOpsy
> ------------------------------------------------------------------------
> oOpsy's Profile:
http://www.excelforum.com/member.php...o&userid=29132
> View this thread: http://www.excelforum.com/showthread...hreadid=488525
>

[oOpsy]

Kaak: Thanks for the code! I'm trying it out but do i place the codes under the module? Sorry abt this

Bob Phillips : Yeap i need to translate 'voltage' into numerals such as '101' cos i'm creating this file whereby it will be read by my labview program which is only capable of reading in numerals . i want to let users to just select 'voltage' instead of typing in all those numerals which makes no sense to them

[Kaak]

It's a function

It should be in a module.
And you can call it from a macro like:

Sub Macro1

ReturnNumber = GetNumberFromString("String 2")

MsgBox(ReturnNumber)

End Sub

Function GetNumberFromString(InputString)

Dim InputString as string
Dim OutputNumber as integer

Select Case InputString

Case "String 1"

OutputNumber = 101

Case "String 2"

OutputNumber = 289

Case "String 3"

OutputNumber = 753

End Select

GetNumberFromString = OutputNumber

End Function


this should return a messagebox with 289 in it

[Bob Phillips]

I think you want event code then. Change the range that the input applies to
and add the cases

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case "voltage": .Value = 101
Case "something": .Value = 200
Case "something else": .Value = 300
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"oOpsy" <oOpsy.1z7u8y_1133196303.5225@excelforum-nospam.com> wrote in
message news:oOpsy.1z7u8y_1133196303.5225@excelforum-nospam.com...
>
> Kaak: Thanks for the code! I'm trying it out but do i place the codes
> under the module? Sorry abt this
>
> Bob Phillips : Yeap i need to translate 'voltage' into numerals such as
> '101' cos i'm creating this file whereby it will be read by my labview
> program which is only capable of reading in numerals . i want to let
> users to just select 'voltage' instead of typing in all those numerals
> which makes no sense to them
>
>
> --
> oOpsy
> ------------------------------------------------------------------------
> oOpsy's Profile:
http://www.excelforum.com/member.php...o&userid=29132
> View this thread: http://www.excelforum.com/showthread...hreadid=488525
>