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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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

J
  • Locked
  • Question Question
formula for randomly choosing an option
Replies
1
Views
135
Fluff
Fluff
D
  • Solved
Refining my code?
2
Replies
16
Views
456
Joe4
Joe4
I
  • Question Question
Advice to remove old code for a new code
Replies
2
Views
192
ipbr21054
I
D
  • Solved
Need help creating a formula based on VBA function!
Replies
5
Views
322
Trixterz
T
M
  • Question Question
VBA changing values in multiple columns - where it should only be changing a single column
Replies
1
Views
345
Akuini
Akuini

Forum statistics

Threads
1,223,268
Messages
6,171,100
Members
452,379
Latest member
IainTru

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