Change alphabetic values into numerical values in VBA

[caroline1991]

I am having a problem and hopw someone is able to help

I want to write a sub where I am able to change alphabetic values into numerical values and then sum these values in a new colume.

An example is as follows

[TABLE="width: 449"]
<tbody>[TR]
[TD]København:[/TD]
[TD]Dårlig[/TD]
[TD]God[/TD]
[TD]Ikke tilgængelig[/TD]
[TD]Fremragende[/TD]
[/TR]
</tbody>[/TABLE]

Dårlig, god, ikke tilgængelig and fremragende each have a numerical value. In the sub I want to change Dårlig, god, ikke tilgængelig and fremragende to 0 and 1 and 0 and 2 and then sum them in Colume E???

I hope this is understandable.

 

[mole999]

are you talking about the combined values of the extended ASCII set (via Char) and then addition, or something in each word that gives it a numerical value

what are your specific conversion factors

 

[caroline1991]

No. What I have is that
God=1
Dårlig=0
Fremragende=2
Ikke tilgængelig=0.

First I tried to make a function, which looked like this: Function opgb(x)

If x = "dårlig" Then
opgb = 0
End If
If x = "neutral" Then
opgb = 0
End If
If x = "ikke tilgængelig" Then
opgb = 0
End If
If x = "god" Then
opgb = 1
End If
If x = "acceptabel" Then
opgb = 1
End If
If x = "enestående" Then
opgb = 2
End If
If x = "fremragende" Then
opgb = 2
End If
End Function

Then I thought it one might be able incorporate the function in the sub?

 

[mole999]

do the words only exist on their own, (lookup)
do words get combined
is there a longer list of words you will be using over time (where a vlookup list might be better)

 

[caroline1991]

I am not sure I understand what you mean. The words dont change or get combined. I just need them to translate into their assigned numerical values in my spreadsheet and then via a sub sum these values on a column

 

[mole999]

maybe try this
i created a new sheet, starting at A1
[TABLE="width: 183"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]dårlig[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]neutral[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ikke tilgængelig[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]god[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]acceptabel[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]enestående[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]fremragende[/TD]
[TD="align: right"]2

[/TD]
[/TR]
</tbody>[/TABLE]

in D1 I put one of your words

I selected A1 to B7 and gave it a named reference "xref"

in E 1 i placed =VLOOKUP(D1,xref,2,0)

 

[caroline1991]

Thank you very much :D