VBA function IF

D

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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
See if this does what you want...

Cells(emptyRow, 11).Value = Switch(X < 1, "Low", X > 5, "High", X, "Medium")
 
Upvote 0
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:
 
Upvote 0
Doflamingo said:
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:
Click to expand...

Can you show us the rest of your code please?
 
Upvote 0
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")
 
Upvote 0
Doflamingo said:
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")
Click to expand...

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.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Excel VBA Print Name Tag from Userform
Replies
2
Views
343
Rhodie72
Rhodie72
M
  • Question Question
Multiple users using same user form having errors and slow at times
Replies
2
Views
527
Joe4
Joe4
M
  • Question Question
Operational Risk Methodology on VBA
Replies
0
Views
81
Mazurofgod
M
M
  • Question Question
Calculating from matrix cells
Replies
1
Views
130
Fluff
Fluff
P
  • Question Question
Multiply Multiple Results
Replies
1
Views
36
JamesCanale
JamesCanale

Forum statistics

Threads
1,223,888
Messages
6,175,212
Members
452,618
Latest member
Tam84

Share this page

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
Back
Top