Excel If Then, create a new column

Solved/Closed
AprilZ - Apr 8, 2011 at 11:37 AM
RWomanizer
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
- Apr 14, 2011 at 01:17 AM
Hello, I'm trying to write a VBA code, because I have too many factors to create a nested function. I can do it fine if there were less than 7 variables. This is my first VBA code, and it is NOT working. And I don't mean that I can close VBA and my macro shows up in the available Macros list. I can't even get to a step where I can make this a Macro that I could select and apply to a column.

This is what I have so far:

Function CalcValue(pVal As String) As Long

If pVal = "0-10% Live Hard Coral, Low Coverage" Then
CalcValue = 1

ElseIf pVal = "0-10% Live Hard Coral, Medium Coverage" Then
CalcValue = 2

ElseIf pVal = "0-10% Live Hard Coral, High Coverage" Then
CalcValue = 3

ElseIf pVal = "10-50% Live Hard Coral, Low Coverage" Then
CalcValue = 4

ElseIf pVal = "10-50% Live Hard Coral, Medium Coverage" Then
CalcValue = 5

ElseIf pVal = "10-50% Live Hard Coral, High Coverage" Then
CalcValue = 6

ElseIf pVal = "50-75% Live Hard Coral, Low Coverage" Then
CalcValue = 7

ElseIf pVal = "50-75% Live Hard Coral, Medium Coverage" Then
CalcValue = 8

ElseIf pVal = "50-75% Live Hard Coral, High Coverage" Then
CalcValue = 9

ElseIf pVal = ">75% Live Hard Coral, Low Coverage" Then
CalcValue = 10

ElseIf pVal = ">75% Live Hard Coral, Medium Coverage" Then
CalcValue = 11

ElseIf pVal = ">75% Live Hard Coral, High Coverage" Then
CalcValue = 12

Else
CalcValue = 0
End If

End Function


MUCH HELP is VERY APPRECIATED!!!
Thank you!
AprilZ


7 replies

RWomanizer
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
Apr 11, 2011 at 03:40 AM
Use function Select Case as

Select Case pVal

Case 0-10% Live Hard Coral, Low Coverage
CalcValue = 1 

Case 0-10% Live Hard Coral, Medium Coverage
CalcValue = 2 

Case 0-10% Live Hard Coral, High Coverage
CalcValue = 3 

Case 10-50% Live Hard Coral, Low Coverage 
CalcValue = 4 

Case 10-50% Live Hard Coral, Medium Coverage
CalcValue = 5 

Case 10-50% Live Hard Coral, High Coverage
CalcValue = 6 

Case 50-75% Live Hard Coral, Low Coverage
CalcValue = 7 

Case 50-75% Live Hard Coral, Medium Coverage
CalcValue = 8 

Case 50-75% Live Hard Coral, High Coverage
CalcValue = 9 

Case >75% Live Hard Coral, Low Coverage
CalcValue = 10 

Case >75% Live Hard Coral, Medium Coverage
CalcValue = 11 

Case >75% Live Hard Coral, High Coverage
CalcValue = 12 

Case Else
CalcValue = 0

End Select
1
Almost there, closer than before. At least now I can close VBA and there is a Macro present that I can select and use, that is error free.
Now, I'm not sure how to select the range for what I want this to evaluate. When I do select a column with Live/Coverage Coral data it gives me "Run-time error '28': Out of stack space"
What I would ideally like to happen would be for me to analyze the column with Live/Coverage Coral data and then the values I want assigned for each class be put into the column next to it. But, If I just have to copy the data into the next column and then run it so that the words change to my numeric values, that would be fine.
0
RWomanizer
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
Apr 12, 2011 at 12:11 AM
Can you specify what is the Range of The your data.

can you put some sample of data here
0
I have a column of data ~ 250 records long. It is called habitat. Values in this column are like those above

0-10% Live Hard Coral, Low Coverage
0-10% Live Hard Coral, Low Coverage
10-50% Live Hard Coral, High Coverage
10-50% Live Hard Coral, High Coverage
0-10% Live Hard Coral, High Coverage
.
.
.

These values are created from Concatenate function.

I would like to assign each Habitat Type (like those listed above) a value, 1-12.

I was having some luck with functions, but if I add more than one If/Then statement it will not run. Also, if I try to copy the function down the row, it returns #NAME?. I like the idea of using a function, because in the column next to my habitat column I would write CalcValue(I2), or WhateverFunction(Habitat column), and it returns my value.
0

Didn't find the answer you are looking for?

Ask a question
RWomanizer
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
Apr 13, 2011 at 12:28 AM
Copy and Paste this user defined funciton in Module of your code :
Public function CalculateValue (rngCell As Range) As double

Dim pVal as String
Dim CalcValue as Double

PVal = rngCell.Value

Select Case pVal

Case 0-10% Live Hard Coral, Low Coverage
CalcValue = 1 

Case 0-10% Live Hard Coral, Medium Coverage
CalcValue = 2 

Case 0-10% Live Hard Coral, High Coverage
CalcValue = 3 

Case 10-50% Live Hard Coral, Low Coverage 
CalcValue = 4 

Case 10-50% Live Hard Coral, Medium Coverage
CalcValue = 5 

Case 10-50% Live Hard Coral, High Coverage
CalcValue = 6 

Case 50-75% Live Hard Coral, Low Coverage
CalcValue = 7 

Case 50-75% Live Hard Coral, Medium Coverage
CalcValue = 8 

Case 50-75% Live Hard Coral, High Coverage
CalcValue = 9 

Case >75% Live Hard Coral, Low Coverage
CalcValue = 10 

Case >75% Live Hard Coral, Medium Coverage
CalcValue = 11 

Case >75% Live Hard Coral, High Coverage
CalcValue = 12 

Case Else
CalcValue = 0

End Select
CalculateValue = CalcValue
End Function


now write the formula in the next cell (J2) of I2 as:

=CalculateValue (I2)

you will get the required result.

Regards,
Rahul
0
Thank you very much! That did it perfectly without any errors. I've never worked with functions, and I kept getting errors, very frustrating.

Thanks again,
AZ
0
RWomanizer
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
Apr 14, 2011 at 01:17 AM
You are Most Welcome!
0