Excel If Then, create a new columnSolved/Closed

Question

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


Configuration: Windows XP / Safari 534.16

RWomanizer · Answer

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

aprilz · Answer

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.

RWomanizer · Answer

Can you specify what is the Range of The your data. 

can you put some sample of data here

AprilZ · Answer

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.

RWomanizer · Answer

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

AprilZ · Answer

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

RWomanizer · Answer

You are Most Welcome!

Excel If Then, create a new column

7 responses

Similar discussions

Subscribe To Our Newsletter!