
Excel VBA Discuss using VBA for Excel programming. 
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com



June 8th, 2008, 01:45 AM

Registered User


Join Date: Jun 2008
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts


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

June 9th, 2008, 09:07 PM

Authorized User


Join Date: Nov 2007
Posts: 48
Thanks: 0
Thanked 4 Times in 4 Posts


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.

June 10th, 2008, 10:34 AM

Registered User


Join Date: Jun 2008
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts


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

June 10th, 2008, 04:29 PM

Authorized User


Join Date: Nov 2007
Posts: 48
Thanks: 0
Thanked 4 Times in 4 Posts


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.

June 10th, 2008, 05:41 PM

Registered User


Join Date: Jun 2008
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts


***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 111(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.

June 10th, 2008, 09:50 PM

Authorized User


Join Date: Nov 2007
Posts: 48
Thanks: 0
Thanked 4 Times in 4 Posts


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.

June 11th, 2008, 10:13 AM

Registered User


Join Date: Jun 2008
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts


thanks.it works great.

November 26th, 2013, 11:28 PM

Registered User


Join Date: Nov 2013
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts


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
Can you please help.
Regards

September 1st, 2017, 04:32 AM

Registered User


Join Date: Sep 2017
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts


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

October 8th, 2017, 04:38 AM

Authorized User


Join Date: Oct 2015
Posts: 48
Thanks: 0
Thanked 5 Times in 5 Posts


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.
__________________
Nostalgia 4 Infinity


