Why I received type mismatch error when running elseif statement?

[tnkh12]

I did not run into any problem when I used `Range` function for a specific observation (e,g, `Range("A4")`. But when I wanted the same result to be replicated for each row, I changed to `cells` function (e.g. `Cells(x,15)`), and received "Type Mismatch" error for ` ElseIf Cells(x, 1) <> "" And Cells(x, 2) <> "" And Cells(x, 15).Value <= 0 Then` Here are my part of my codes. I have tried hours debugging it but to no avail. Any help?

Dim x As Integer
For x = 4 To 100
            If Cells(x, 1) <> "" And Cells(x, 2) <> "" And Cells(x, 6).Value <= 0 Then                  
               Cells(x, 16).Value = 6
               Cells(x, 17).Value = -0.3179688
                            
                        ElseIf Cells(x, 1) <> "" And Cells(x, 2) <> "" And Cells(x, 15).Value <= 0 Then (*Received error for this line)
                            Cells(x, 16).Value = 1
                            Cells(x, 17).Value = 0.6820312
                                              
                        ElseIf Cells(x, 1).Value = "A. Agriculture, forestry and fishing" Then
                            Select Case LCase(Cells(x, 2).Value)
                                Case "all", "id", "sg"
                                        Select Case Cells(x, 15).Value
                                            Case Is > 4
                                                Cells(x, 16).Value = 5
                                                Cells(x, 17).Value = -0.2405524
                                            Case 2.01 To 4
                                                Cells(x, 16).Value = 4
                                                Cells(x, 17).Value = 0.0223717
                                            Case 1.01 To 2
                                                Cells(x, 16).Value = 3
                                                Cells(x, 17).Value = 0.112231
                                            Case 0.01 To 1
                                                Cells(x, 16).Value = 2
                                                Cells(x, 17).Value = 0.5928195
                                        End Select
                                Case "my", "th"
                                        Select Case Cells(x, 15).Value
                                            Case Is > 4.5
                                                Cells(x, 16).Value = 5
                                                Cells(x, 17).Value = -0.2405524
                                            Case 2.01 To 4.5
                                                Cells(x, 16).Value = 4
                                                Cells(x, 17).Value = 0.0223717
                                            Case 1.01 To 2
                                                Cells(x, 16).Value = 3
                                                Cells(x, 17).Value = 0.112231
                                            Case 0.01 To 1
                                                Cells(x, 16).Value = 2
                                                Cells(x, 17).Value = 0.5928195
                                        End Select
                                Case ""
                                    Cells(x, 16).Value = ""
                                    Cells(x, 17).Value = ""
                            End Select
             End if

Next x

End Sub

 

[Michael M]

try putting .Value after the cell reference
seems to work ok for me

ElseIf Cells(x, 1).value <> "" And Cells(x, 2).value <> "" And Cells(x, 15).Value <= 0 Then

 

[tnkh12]

Doesn't work. For information, the cell (x,15) contains an existing formula of

=(D4+E4)F4

in the spreadsheet. Not sure whether that's the source of issue.

 

[Michael M]

Hmm, used your formula in x,15 AND added the .Value....and it worked fine for me !!
Have you tried using it in a clean version of the workbook....copy the Macro.... close the workbook without saving....reopen and paste the code back in.
Sometimes the VBE will collect some invisible JUNK and screw the codes up.

 

[tnkh12]

I noticed what causes the problem. Code runs totally fine if all cells in

=(D4+E4)F4

are filled. But if anyone of them isn't filled, I got this mismatch error. What code should I put in so that if anyone of these three cells is not filled,

cells(x,15)

will return nothing, and would not give me error?

 

[Michael M]

Depending on how they are populated, you use something like this

Sub MM1()
Dim x As Integer
For x = 4 To 100
           If Cells(x, 1) = "" Or Cells(x, 2) = "" Or Cells(x, 15).Value = "" Or Cells(x, 6).Value = "" Then
           MsgBox "Some Cells are Missing data !!!....Please Retry Process !", vbOKOnly
           Exit Sub
           End If
            If Cells(x, 1) <> "" And Cells(x, 2) <> "" And Cells(x, 6).Value <= 0 Then
               Cells(x, 16).Value = 6
               Cells(x, 17).Value = -0.3179688
                            
                        ElseIf Cells(x, 1) <> "" And Cells(x, 2) <> "" And Cells(x, 15).Value <= 0 Then
                            Cells(x, 16).Value = 1
                            Cells(x, 17).Value = 0.6820312
                                              
                        ElseIf Cells(x, 1).Value = "A. Agriculture, forestry and fishing" Then
                            Select Case LCase(Cells(x, 2).Value)
                                Case "all", "id", "sg"
                                        Select Case Cells(x, 15).Value
                                            Case Is > 4
                                                Cells(x, 16).Value = 5
                                                Cells(x, 17).Value = -0.2405524
                                            Case 2.01 To 4
                                                Cells(x, 16).Value = 4
                                                Cells(x, 17).Value = 0.0223717
                                            Case 1.01 To 2
                                                Cells(x, 16).Value = 3
                                                Cells(x, 17).Value = 0.112231
                                            Case 0.01 To 1
                                                Cells(x, 16).Value = 2
                                                Cells(x, 17).Value = 0.5928195
                                        End Select
                                Case "my", "th"
                                        Select Case Cells(x, 15).Value
                                            Case Is > 4.5
                                                Cells(x, 16).Value = 5
                                                Cells(x, 17).Value = -0.2405524
                                            Case 2.01 To 4.5
                                                Cells(x, 16).Value = 4
                                                Cells(x, 17).Value = 0.0223717
                                            Case 1.01 To 2
                                                Cells(x, 16).Value = 3
                                                Cells(x, 17).Value = 0.112231
                                            Case 0.01 To 1
                                                Cells(x, 16).Value = 2
                                                Cells(x, 17).Value = 0.5928195
                                        End Select
                                Case ""
                                    Cells(x, 16).Value = ""
                                    Cells(x, 17).Value = ""
                            End Select
             End If

Next x

End Sub