Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old June 8th, 2008, 01:45 AM
Registered User
 
Join Date: Jun 2008
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
Reply With Quote
  #2 (permalink)  
Old June 9th, 2008, 09:07 PM
Authorized User
Points: 193, Level: 3
Points: 193, Level: 3 Points: 193, Level: 3 Points: 193, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2007
Location: North East Pennsylvania, USA.
Posts: 48
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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.
Reply With Quote
  #3 (permalink)  
Old June 10th, 2008, 10:34 AM
Registered User
 
Join Date: Jun 2008
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



Reply With Quote
  #4 (permalink)  
Old June 10th, 2008, 04:29 PM
Authorized User
Points: 193, Level: 3
Points: 193, Level: 3 Points: 193, Level: 3 Points: 193, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2007
Location: North East Pennsylvania, USA.
Posts: 48
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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.
Reply With Quote
  #5 (permalink)  
Old June 10th, 2008, 05:41 PM
Registered User
 
Join Date: Jun 2008
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #6 (permalink)  
Old June 10th, 2008, 09:50 PM
Authorized User
Points: 193, Level: 3
Points: 193, Level: 3 Points: 193, Level: 3 Points: 193, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2007
Location: North East Pennsylvania, USA.
Posts: 48
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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.
Reply With Quote
  #7 (permalink)  
Old June 11th, 2008, 10:13 AM
Registered User
 
Join Date: Jun 2008
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thanks.it works great.

Reply With Quote
  #8 (permalink)  
Old November 26th, 2013, 10:28 PM
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2013
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
Reply With Quote
  #9 (permalink)  
Old September 1st, 2017, 04:32 AM
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2017
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #10 (permalink)  
Old October 8th, 2017, 04:38 AM
Authorized User
Points: 162, Level: 3
Points: 162, Level: 3 Points: 162, Level: 3 Points: 162, Level: 3
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Oct 2015
Location: South Africa
Posts: 38
Thanks: 0
Thanked 4 Times in 4 Posts
Default 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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Pull data values & assign them to a text control cesemj ASP.NET 2.0 Basics 0 March 12th, 2008 06:54 AM
Getting Numerical Values Fiddleman HTML Code Clinic 2 September 13th, 2004 02:51 PM
How to extract numerical values from string? Genuine Beginning PHP 7 August 7th, 2004 07:49 AM
textbox with numerical values must C# 1 August 7th, 2003 12:44 PM



All times are GMT -4. The time now is 08:47 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.