No response when using if-elseif function

T

tnkh12

New Member
Joined
Feb 28, 2016
Messages
6
I have a project due in 5 days. I am trying to create a tool using macro so that when few criteria are selected, a certain rating will be shown. There are three criteria that need to be filtered before getting the result. For example, if the industry (eg. agriculture) is selected, followed by selection of countries (e.g Indonesia), followed by selection of certain ratio (e.g.2.5), a rating from 1 to 6 will be given (in this case, 3). I tried the following code ,and nothing appears under my rating column but there is no error message. Can someone please help me out. Codes are shown below.
Code: 
Private Sub CommandButton1_Click()
 If Range("V4").Value = "A.Agriculture,forestry and fishing" Then

  If Range("W4").Value = All Or ID Or SG Then

    If Range("D4").Value <= 0 Then
        Range("X4").Value = 6
    ElseIf Range("M4").Value > 4 Then
        Range("X4").Value = 5
    ElseIf Range("M4").Value <= 4 And Value > 2 Then
        Range("X4").Value = 4
    ElseIf Range("M4").Value <= 2 And Value > 1 Then
        Range("X4").Value = 3
    ElseIf Range("M4").Value <= 1 And Value > 0 Then
        Range("X4").Value = 2
    ElseIf Range("M4").Value <= 0 Then
        Range("X4").Value = 1
    End If

  ElseIf Range("W4").Value = MY Or TH Then

    If Range("D4").Value <= 0 Then
        Range("X4").Value = 6
    ElseIf Range("M4").Value > 4.5 Then
        Range("X4").Value = 5
    ElseIf Range("M4").Value <= 4.5 And Value > 2 Then
        Range("X4").Value = 4
    ElseIf Range("M4").Value <= 2 And Value > 1 Then
        Range("X4").Value = 3
    ElseIf Range("M4").Value <= 1 And Value > 0 Then
        Range("X4").Value = 2
    ElseIf Range("M4").Value <= 0 Then
        Range("X4").Value = 1
    End If
Else: Range("X4").Value = ""
End If
Else If Range("V4").Value = "B.Mining and quarrying" Then

If Range("W4").Value = All Or ID Or MY Or SG Or TH Then

    If Range("D4").Value <= 0 Then
        Range("X4").Value = 6
    ElseIf Range("M4").Value > 3.5 Then
        Range("X4").Value = 5
    ElseIf Range("M4").Value <= 3.5 And Value > 2 Then
        Range("X4").Value = 4
    ElseIf Range("M4").Value <= 2 And Value > 1 Then
        Range("X4").Value = 3
    ElseIf Range("M4").Value <= 1 And Value > 0 Then
        Range("X4").Value = 2
    ElseIf Range("M4").Value <= 0 Then
        Range("X4").Value = 1
    End If

Else: Range("X4").Value = ""
End If

End If
End Sub
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
Lines like this

Code: 
If Range("W4").Value = All Or ID Or SG Then

Should be

Code: 
If Range("W4").Value = "All" or Range("W4").Value = "ID" or Range("W4").Value = "SG" then

Also, lines like

Code: 
ElseIf Range("M4").Value <= 4 And Value > 2 Then

probably should be


Code: 
ElseIf Range("M4").Value <= 4 And Range("M4").Value > 2 Then
 
Last edited:
Upvote 0
sericom said:
Lines like this

Code: 
If Range("W4").Value = All Or ID Or SG Then

Should be

Code: 
If Range("W4").Value = "All" or Range("W4").Value = "ID" or Range("W4").Value = "SG" then

Also, lines like

Code: 
ElseIf Range("M4").Value <= 4 And Value > 2 Then

probably should be


Code: 
ElseIf Range("M4").Value <= 4 And Range("M4").Value > 2 Then
Click to expand...

Thanks but I tried the above codes and there is still no result shown.
 
Upvote 0
Code: 
[color=darkblue]Private[/color] [color=darkblue]Sub[/color] CommandButton1_Click()
    [color=darkblue]If[/color] Range("V4").Value = "A.Agriculture,forestry and fishing" [color=darkblue]Then[/color]

        [color=darkblue]If[/color] Range("W4").Value = "All" [color=darkblue]Or[/color] Range("W4").Value = "ID" [color=darkblue]Or[/color] Range("W4").Value = "SG" [color=darkblue]Then[/color]

            [color=darkblue]If[/color] Range("D4").Value <= 0 [color=darkblue]Then[/color]
                Range("X4").Value = 6
            [color=darkblue]ElseIf[/color] Range("M4").Value > 4 [color=darkblue]Then[/color]
                Range("X4").Value = 5
            [color=darkblue]ElseIf[/color] Range("M4").Value <= 4 And Range("M4").Value > 2 [color=darkblue]Then[/color]
                Range("X4").Value = 4
            [color=darkblue]ElseIf[/color] Range("M4").Value <= 2 And Range("M4").Value > 1 [color=darkblue]Then[/color]
                Range("X4").Value = 3
            [color=darkblue]ElseIf[/color] Range("M4").Value <= 1 And Range("M4").Value > 0 [color=darkblue]Then[/color]
                Range("X4").Value = 2
            [color=darkblue]ElseIf[/color] Range("M4").Value <= 0 [color=darkblue]Then[/color]
                Range("X4").Value = 1
            [color=darkblue]End[/color] [color=darkblue]If[/color]

        [color=darkblue]ElseIf[/color] Range("W4").Value = "MY" [color=darkblue]Or[/color] Range("W4").Value = "TH" [color=darkblue]Then[/color]

            [color=darkblue]If[/color] Range("D4").Value <= 0 [color=darkblue]Then[/color]
                Range("X4").Value = 6
            [color=darkblue]ElseIf[/color] Range("M4").Value > 4.5 [color=darkblue]Then[/color]
                Range("X4").Value = 5
            [color=darkblue]ElseIf[/color] Range("M4").Value <= 4.5 And Range("M4").Value > 2 [color=darkblue]Then[/color]
                Range("X4").Value = 4
            [color=darkblue]ElseIf[/color] Range("M4").Value <= 2 And Range("M4").Value > 1 [color=darkblue]Then[/color]
                Range("X4").Value = 3
            [color=darkblue]ElseIf[/color] Range("M4").Value <= 1 And Range("M4").Value > 0 [color=darkblue]Then[/color]
                Range("X4").Value = 2
            [color=darkblue]ElseIf[/color] Range("M4").Value <= 0 [color=darkblue]Then[/color]
                Range("X4").Value = 1
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        Else: Range("X4").Value = ""
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]ElseIf[/color] Range("V4").Value = "B.Mining and quarrying" [color=darkblue]Then[/color]

        [color=darkblue]If[/color] Range("W4").Value = "All" [color=darkblue]Or[/color] Range("W4").Value = "ID" [color=darkblue]Or[/color] Range("W4").Value = "MY" _
        [color=darkblue]Or[/color] Range("W4").Value = "SG" [color=darkblue]Or[/color] Range("W4").Value = "TH" [color=darkblue]Then[/color]

            [color=darkblue]If[/color] Range("D4").Value <= 0 [color=darkblue]Then[/color]
                Range("X4").Value = 6
            [color=darkblue]ElseIf[/color] Range("M4").Value > 3.5 [color=darkblue]Then[/color]
                Range("X4").Value = 5
            [color=darkblue]ElseIf[/color] Range("M4").Value <= 3.5 And Range("M4").Value > 2 [color=darkblue]Then[/color]
                Range("X4").Value = 4
            [color=darkblue]ElseIf[/color] Range("M4").Value <= 2 And Range("M4").Value > 1 [color=darkblue]Then[/color]
                Range("X4").Value = 3
            [color=darkblue]ElseIf[/color] Range("M4").Value <= 1 And Range("M4").Value > 0 [color=darkblue]Then[/color]
                Range("X4").Value = 2
            [color=darkblue]ElseIf[/color] Range("M4").Value <= 0 [color=darkblue]Then[/color]
                Range("X4").Value = 1
            [color=darkblue]End[/color] [color=darkblue]If[/color]

        Else: Range("X4").Value = ""
        [color=darkblue]End[/color] [color=darkblue]If[/color]

    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]
 
Upvote 0
MARK858 said:
Code: 
[color=darkblue]Private[/color] [color=darkblue]Sub[/color] CommandButton1_Click()
    [color=darkblue]If[/color] Range("V4").Value = "A.Agriculture,forestry and fishing" [color=darkblue]Then[/color]

        [color=darkblue]If[/color] Range("W4").Value = "All" [color=darkblue]Or[/color] Range("W4").Value = "ID" [color=darkblue]Or[/color] Range("W4").Value = "SG" [color=darkblue]Then[/color]

            [color=darkblue]If[/color] Range("D4").Value <= 0 [color=darkblue]Then[/color]
                Range("X4").Value = 6
            [color=darkblue]ElseIf[/color] Range("M4").Value > 4 [color=darkblue]Then[/color]
                Range("X4").Value = 5
            [color=darkblue]ElseIf[/color] Range("M4").Value <= 4 And Range("M4").Value > 2 [color=darkblue]Then[/color]
                Range("X4").Value = 4
            [color=darkblue]ElseIf[/color] Range("M4").Value <= 2 And Range("M4").Value > 1 [color=darkblue]Then[/color]
                Range("X4").Value = 3
            [color=darkblue]ElseIf[/color] Range("M4").Value <= 1 And Range("M4").Value > 0 [color=darkblue]Then[/color]
                Range("X4").Value = 2
            [color=darkblue]ElseIf[/color] Range("M4").Value <= 0 [color=darkblue]Then[/color]
                Range("X4").Value = 1
            [color=darkblue]End[/color] [color=darkblue]If[/color]

        [color=darkblue]ElseIf[/color] Range("W4").Value = "MY" [color=darkblue]Or[/color] Range("W4").Value = "TH" [color=darkblue]Then[/color]

            [color=darkblue]If[/color] Range("D4").Value <= 0 [color=darkblue]Then[/color]
                Range("X4").Value = 6
            [color=darkblue]ElseIf[/color] Range("M4").Value > 4.5 [color=darkblue]Then[/color]
                Range("X4").Value = 5
            [color=darkblue]ElseIf[/color] Range("M4").Value <= 4.5 And Range("M4").Value > 2 [color=darkblue]Then[/color]
                Range("X4").Value = 4
            [color=darkblue]ElseIf[/color] Range("M4").Value <= 2 And Range("M4").Value > 1 [color=darkblue]Then[/color]
                Range("X4").Value = 3
            [color=darkblue]ElseIf[/color] Range("M4").Value <= 1 And Range("M4").Value > 0 [color=darkblue]Then[/color]
                Range("X4").Value = 2
            [color=darkblue]ElseIf[/color] Range("M4").Value <= 0 [color=darkblue]Then[/color]
                Range("X4").Value = 1
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        Else: Range("X4").Value = ""
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]ElseIf[/color] Range("V4").Value = "B.Mining and quarrying" [color=darkblue]Then[/color]

        [color=darkblue]If[/color] Range("W4").Value = "All" [color=darkblue]Or[/color] Range("W4").Value = "ID" [color=darkblue]Or[/color] Range("W4").Value = "MY" _
        [color=darkblue]Or[/color] Range("W4").Value = "SG" [color=darkblue]Or[/color] Range("W4").Value = "TH" [color=darkblue]Then[/color]

            [color=darkblue]If[/color] Range("D4").Value <= 0 [color=darkblue]Then[/color]
                Range("X4").Value = 6
            [color=darkblue]ElseIf[/color] Range("M4").Value > 3.5 [color=darkblue]Then[/color]
                Range("X4").Value = 5
            [color=darkblue]ElseIf[/color] Range("M4").Value <= 3.5 And Range("M4").Value > 2 [color=darkblue]Then[/color]
                Range("X4").Value = 4
            [color=darkblue]ElseIf[/color] Range("M4").Value <= 2 And Range("M4").Value > 1 [color=darkblue]Then[/color]
                Range("X4").Value = 3
            [color=darkblue]ElseIf[/color] Range("M4").Value <= 1 And Range("M4").Value > 0 [color=darkblue]Then[/color]
                Range("X4").Value = 2
            [color=darkblue]ElseIf[/color] Range("M4").Value <= 0 [color=darkblue]Then[/color]
                Range("X4").Value = 1
            [color=darkblue]End[/color] [color=darkblue]If[/color]

        Else: Range("X4").Value = ""
        [color=darkblue]End[/color] [color=darkblue]If[/color]

    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]
Click to expand...

Thank you. I managed to sort this out.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
After User Enters A Value (Validation List), Not Automatically Advancing To Next Cell As Per "Select"
Replies
1
Views
166
Ark68
A
R
  • Question
How to combine the functions of having the ability to select multiple items from a drop down list and hide columns based on values of said list?
Replies
0
Views
210
rgree05
R
D
  • Solved
Combining two INDEX and MATCH functions
Replies
3
Views
252
felixstraube
F
D
  • Question
Adapt IF AND ISBLANK formula combination in relation to UNIQUE FILTER on another sheet
2
Replies
15
Views
619
KRice
K
S
  • Question
New record from multi sheets to one sheet
Replies
0
Views
200
spmatrix
S

Forum statistics

Threads
1,214,620
Messages
6,120,559
Members
448,970
Latest member
kennimack

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