Excel If Then, create a new column [Solved/Closed]

Report
-
Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
-
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

Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

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.
Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
Can you specify what is the Range of The your data.

can you put some sample of data here
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.
Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
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
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
Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
You are Most Welcome!