p2p.wrox.com Forums (http://p2p.wrox.com/)
-   Excel VBA (http://p2p.wrox.com/excel-vba-79/)
-   -   assign numerical values to text (http://p2p.wrox.com/excel-vba/68658-assign-numerical-values-text.html)

 ptrussell2009 June 8th, 2008 01:45 AM

assign numerical values to text

hey i need to make it possible to take text from 3 different drop down boxes, assign them a numerical value, then have the values added together and displayed in a different cell.

for example if from the drop down box in cell a1 was the word 'dog', and the text in b1 was 'cat' and the text in c1 was 'goat' then i need dog to be set equal to 6, cat set equal to 3, and goat set equal to 1. THEN have those values added together and be placed in cell d1.any help would be great.thanks

a b c d
1 dog cat goat 10
2
3
4
5

 stanleydgromjr June 9th, 2008 09:07 PM

ptrussell2009,

Welcome to the board.

We would need to see all the words in each drop down list for column A, B, and C, and their corresponding values.

Have a great day,
Stan

stanleydgromjr

Windows Vista Business and Excel 2003, 2007.

 ptrussell2009 June 10th, 2008 10:34 AM

I actually have figured it out, BUT i only know how to do it one cell at a time. These are the objects in the drop down list and their corresponding values:

Environment Impact Cycle
xDE=1 1=1 ITC1=1
xDV=2 2=2 ITC2=2
xO1=3 3=3 PR1=3
xOQ=4 4=4 PR2=4
xOV=5 5=5
xR1=6
xRB=7
xRP=8
xSB=9
xTO=10
xTS=11

Here is how I have been doing it one at a time(Im not worried about adding the Cycle field right now thats why its not in the code):

Sub Priority()
Dim temp As Integer

With ActiveSheet

If .Range("H9") = ("xDE") Then
temp = 1
End If
If .Range("H9") = ("xDV") Then
temp = 2
End If
If .Range("H9") = ("xO1") Then
temp = 3
End If
If .Range("H9") = ("xOQ") Then
temp = 4
End If
If .Range("H9") = ("xOV") Then
temp = 5
End If
If .Range("H9") = ("xR1") Then
temp = 6
End If
If .Range("H9") = ("xRB") Then
temp = 7
End If
If .Range("H9") = ("xRP") Then
temp = 8
End If
If .Range("H9") = ("xSB") Then
temp = 9
End If
If .Range("H9") = ("xTO") Then
.Range("O17") = 10
End If
If .Range("H9") = ("xTS") Then
temp = 11
End If

'............IMPACT..............
If .Range("I9") = ("1") Then
temp2 = 1
End If
If .Range("I9") = ("2") Then
temp2 = 2
End If
If .Range("I9") = ("3") Then
temp2 = 3
End If
If .Range("I9") = ("4") Then
temp2 = 4
End If
If .Range("I9") = ("5") Then
temp2 = 5
End If

'......test.............
.Range("F9") = temp + temp2

End With
End Sub

Ive been working on something like this so that I can do it for more than just one cell.I need it to work for about 1000.

Last_Row = Range("H1000").End(xlUp).Row

For H = 1 To Last_Row
If Cells(H, 1) = ("xDE") Then
Cells(H, 2) = 1

End If
Next H

 stanleydgromjr June 10th, 2008 04:29 PM

ptrussell2009,

What are the actual columns being used to select the drop down lists, and the total column?
? ? ? ?
1 xOQ 2 PR2 10
2
3
4
5

Have a great day,
Stan

stanleydgromjr

Windows Vista Business and Excel 2003, 2007.

 ptrussell2009 June 10th, 2008 05:41 PM

***Dont worry about the Description colum.
F G H I J
Priority Description Environment Impact Cycle
1
2
3
4
5

When you click on cell F1 a drop down box comes up with xDE,xDV etc. In the macro i have assigned each one a value of 1-11(look at my previous post).so when the user selects xDE the value of 1 is stored in a temp variable in the macro but what is displayed on the spreadsheet is xDE. The same goes for Impact and Cycle. The sum of Environment, Impact, and Cycle goes into the Priority column.

This is how it should work:
Click on cell H1 and choose an Environment for the drop down list.
Click on cell I1 and choose an Impact from the drop down list
Click on cell J1 and choose a Cycle from the drop down list.
Run the macro
The value in cell F1 should be the sum of H1+I1+J1
Next I would go to cell H2 and do the same thing all over again.

 stanleydgromjr June 10th, 2008 09:50 PM

ptrussell2009,

This works in my test environment. I added a blank row at the top of each drop down list.

Try this formula in cell K1 (then copy it down):
=LOOKUP(H1,{0,"xDE","xDV","xO1","xOQ","xOV","xR1", "xRB","xRP","xSB","xTO","xTS"},{0,1,2,3,4,5,6,7,8, 9,10,11})+LOOKUP(I1,{0,1,2,3,4,5},{0,1,2,3,4,5})+L OOKUP(J1,{0,"ITC1","ITC2","PR1","PR2"},{0,1,2,3,4} )

As you change the drop down selections in column H, I, and J, the total will appear in column K, for each row.

Have a great day,
Stan

stanleydgromjr

Windows Vista Business and Excel 2003, 2007.

 ptrussell2009 June 11th, 2008 10:13 AM

thanks.it works great.

 s.lucky November 26th, 2013 10:28 PM

Change Value to Number and get the sum

Hi,
This is regarding excel data formating,
I want to take the values from 10 different fields which will have the values as per the drop down and then sum its correspending rating

I found very good solution which has been very useful, however, the field value is not giving me the exact result as per the values in Lookup vector and result vector.
For Eg. If im giving ::
=LOOKUP(C2,{"","AE","OE","NE","BE"},{0,5,3,1,0})
and if i pick NE from drop down, it takes the value 5

Regards

 wagon6urn3r September 1st, 2017 04:32 AM

It's been awhile since your post, and if you have not figured it out yet you might try something like this:

Sub ranCol()
Dim cell As Range
For Each cell In Range("A2:A52, B2:B52")
If cell.value = "Y" Then
cell.value = 2
ElseIf cell.value = "N" Then
cell.value = 1
ElseIf cell.value = "" Then
cell.value = 0
ElseIf cell.value = "y" Then
cell.value = 2
ElseIf cell.value = "n" Then
cell.value = 1
ElseIf cell.value = "" Then
cell.value = 0
End If
Next
End Sub

 Zakalwe October 8th, 2017 04:38 AM

Alternatively Use a collection or a File

Hi,

I believe a collection with these codes as keys and the numbers as values will work best.

Alternatively have the collection reading from a file of keys and value pairs.

 All times are GMT -4. The time now is 07:57 PM.