VBA function IF

Doflamingo

Board Regular
Joined
Apr 16, 2019
Messages
238
Hi all,

I try to make appear on the cell

Code:
Cells(emptyRow, 11)

the value ''HIGH'', ''MEDIUM'' or ''LOW'' in function of the value of the cell

Code:
Cells(emptyRow, 10).value

I mean if the value of that cell is more than 6 the value of Cells(emptyRow, 11) is ''HIGH'', if its value is less than 1 the value of Cells(emptyRow, 11) is ''LOW'' and if the value of Cells(emptyRow, 10) is between 1 and 6, the value of Cells(emptyRow, 11) is ''MEDIUM''.

Any ideas ? :confused:
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,968
Office Version
  1. 2016
Platform
  1. Windows
See if this does what you want...

Cells(emptyRow, 11).Value = Switch(X < 1, "Low", X > 5, "High", X, "Medium")
 

Doflamingo

Board Regular
Joined
Apr 16, 2019
Messages
238
Hello @Rick Rothstein

Thanks for your answer, it works for the value ''Low'' but I don't know why it always put ''Low'' even if the value of Cells(emptyRow, 10) is more than 6

Here is the code

Code:
Cells(emptyRow, 11).Select
Cells(emptyRow, 11).Value = Switch(X < 1, "Low", X > 5, "High", X, "Medium")

Any ideas ? :confused:
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,968
Office Version
  1. 2016
Platform
  1. Windows
Hello @Rick Rothstein

Thanks for your answer, it works for the value ''Low'' but I don't know why it always put ''Low'' even if the value of Cells(emptyRow, 10) is more than 6

Here is the code

Code:
Cells(emptyRow, 11).Select
Cells(emptyRow, 11).Value = Switch(X < 1, "Low", X > 5, "High", X, "Medium")

Any ideas ? :confused:

Can you show us the rest of your code please?
 

Doflamingo

Board Regular
Joined
Apr 16, 2019
Messages
238

ADVERTISEMENT

Hello @Rick Rothstein

Here is the code where the value of Cells(emptyRow, 11) with ''HIGH'', ''Medium'' or ''Low'' is based on the value of Cells(emptyRow, 10) which is computed with the value of Cells(emptyRow, 19), Cells(emptyRow, 18) and ..., until Cells(emptyRow, 4)

Code:
Cells(emptyRow, 10).Select
Cells(emptyRow, 10).Value = (Cells(emptyRow, 3).Value + Cells(emptyRow, 4).Value + Cells(emptyRow, 5).Value + Cells(emptyRow, 6).Value + Cells(emptyRow, 7).Value + Cells(emptyRow, 8).Value + Cells(emptyRow, 9).Value) / 7


Cells(emptyRow, 11).Select
Cells(emptyRow, 11).Value = Switch(X < 1, "Low", X > 5, "High", X, "Medium")
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,968
Office Version
  1. 2016
Platform
  1. Windows
Hello @Rick Rothstein

Here is the code where the value of Cells(emptyRow, 11) with ''HIGH'', ''Medium'' or ''Low'' is based on the value of Cells(emptyRow, 10) which is computed with the value of Cells(emptyRow, 19), Cells(emptyRow, 18) and ..., until Cells(emptyRow, 4)

Code:
Cells(emptyRow, 10).Select
Cells(emptyRow, 10).Value = (Cells(emptyRow, 3).Value + Cells(emptyRow, 4).Value + Cells(emptyRow, 5).Value + Cells(emptyRow, 6).Value + Cells(emptyRow, 7).Value + Cells(emptyRow, 8).Value + Cells(emptyRow, 9).Value) / 7


Cells(emptyRow, 11).Select
Cells(emptyRow, 11).Value = Switch(X < 1, "Low", X > 5, "High", X, "Medium")

I wanted to see your actual, full code, mainly to see where and how X is being assigned a value, but also to see if anything else in your code might be affecting things.
 

Doflamingo

Board Regular
Joined
Apr 16, 2019
Messages
238
Hi @Rick Rothstein,

Here is the entire code of the userform I use

Code:
Private Sub CommandButton1_Click()
Dim emptyRow As Long


Sheet3.Activate


emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
'SERVICE PROVIDER
Cells(emptyRow, 1).Value = TextBox1.Value
Cells(emptyRow, 1).Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With


'CATEGORY
Cells(emptyRow, 2).Value = ListBox1.Value
Cells(emptyRow, 2).Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With


'DELEGATED ACTIVITY
Cells(emptyRow, 3).Value = OptionButton1.Value
Cells(emptyRow, 3).Value = OptionButton2.Value


If OptionButton1.Value = True Then
    Cells(emptyRow, 3).Value = "0"
End If


If OptionButton2.Value = True Then
    Cells(emptyRow, 3).Value = "-2"
End If


Cells(emptyRow, 3).Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With




'RECURRENT SERVICE


Cells(emptyRow, 4).Value = OptionButton3.Value
Cells(emptyRow, 4).Value = OptionButton4.Value
Cells(emptyRow, 4).Value = OptionButton5.Value


If OptionButton3.Value = True Then
    Cells(emptyRow, 4).Value = "0"
End If


If OptionButton4.Value = True Then
    Cells(emptyRow, 4).Value = "-1"
End If


If OptionButton5.Value = True Then
    Cells(emptyRow, 4).Value = "-2"
End If


Cells(emptyRow, 4).Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With




'LISTED ENTITY


Cells(emptyRow, 5).Value = OptionButton6.Value
Cells(emptyRow, 5).Value = OptionButton7.Value
Cells(emptyRow, 5).Value = OptionButton8.Value




If OptionButton6.Value = True Then
    Cells(emptyRow, 5).Value = "1"
End If


If OptionButton7.Value = True Then
    Cells(emptyRow, 5).Value = "0"
End If


If OptionButton8.Value = True Then
    Cells(emptyRow, 5).Value = "-1"
End If


Cells(emptyRow, 5).Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With




'REGULATED ENTITY
Cells(emptyRow, 6).Value = OptionButton9.Value
Cells(emptyRow, 6).Value = OptionButton10.Value
Cells(emptyRow, 6).Value = OptionButton11.Value


If OptionButton9.Value = True Then
    Cells(emptyRow, 6).Value = "1"
End If


If OptionButton10.Value = True Then
    Cells(emptyRow, 6).Value = "0"
End If


If OptionButton11.Value = True Then
    Cells(emptyRow, 6).Value = "-1"
End If


Cells(emptyRow, 6).Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With


'SANCTION


Cells(emptyRow, 7).Value = OptionButton12.Value
Cells(emptyRow, 7).Value = OptionButton13.Value
Cells(emptyRow, 7).Value = OptionButton14.Value


If OptionButton12.Value = True Then
    Cells(emptyRow, 7).Value = "1"
End If


If OptionButton13.Value = True Then
    Cells(emptyRow, 7).Value = "0"
End If


If OptionButton14.Value = True Then
    Cells(emptyRow, 7).Value = "-1"
End If


Cells(emptyRow, 7).Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With


'FINANCIAL STABILITY


Cells(emptyRow, 8).Value = OptionButton15.Value
Cells(emptyRow, 8).Value = OptionButton16.Value


If OptionButton15.Value = True Then
    Cells(emptyRow, 8).Value = "1"
End If


If OptionButton16.Value = True Then
    Cells(emptyRow, 8).Value = "-1"
End If


Cells(emptyRow, 8).Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With




'COUNTRY RISK


Cells(emptyRow, 9).Value = OptionButton17.Value
Cells(emptyRow, 9).Value = OptionButton18.Value
Cells(emptyRow, 9).Value = OptionButton19.Value


If OptionButton17.Value = True Then
    Cells(emptyRow, 9).Value = "1"
End If


If OptionButton18.Value = True Then
    Cells(emptyRow, 9).Value = "0"
End If


If OptionButton19.Value = True Then
    Cells(emptyRow, 9).Value = "-1"
End If


Cells(emptyRow, 9).Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    
Cells(emptyRow, 10).Select
Cells(emptyRow, 10).Value = (Cells(emptyRow, 3).Value + Cells(emptyRow, 4).Value + Cells(emptyRow, 5).Value + Cells(emptyRow, 6).Value + Cells(emptyRow, 7).Value + Cells(emptyRow, 8).Value + Cells(emptyRow, 9).Value) / 7


Cells(emptyRow, 11).Select
Cells(emptyRow, 11).Value = Switch(X < 1, "Low", X > 5, "High", X, "Medium")
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,317
Messages
5,635,527
Members
416,862
Latest member
MGDlite

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top