Runtime Error 13 Type Mismatch

**vtsoldier2010** · 03-19-2012, 07:44 AM

Dear Forum,

I'm having a bit of an issue with a script I'm writing, and I'm hoping that someone here can help. My issue is i'm getting a runtime error 13 Type Mismatch when I try to run my code. I think the issue lies in the fact that i want my script to be ran on every cell in the column because if i run my script on just one cell say "s2" it works just fine. However, when I expand the cell range reference to capture the entire oolumn I get the error. I read somewhere blank cells in the columns where my script is interacting with the data validation lists could create a problem, and it also could be that the compiler expects one data type, but is seeing another data type. Here's my code, hopefully someone can help me make sense of what i'm doing wrong. I've been working on this project for a week now, and this is the last hurdle to clear before it's completed. Any help you guys can give is greatly appreciated.

Private Sub Worksheet_Change(ByVal Target As Range) 
    Set Target = Intersect(Target, Range("q2:s1048576")) 
    If (Not Target Is Nothing) Then 
        Target.Offset(0, 1).Value = "" 
    End If 
    If Range("q2:q1048576").Value = "PLX" And Range("r2:r1048576").Value = "Imaging_TopCall" And Range("s2:s1048576").Value = "Missing From File" Then         Range("u2:u1048576").Value = "Level 1" 
    ElseIf Range("q2:q1048576").Value = "Document" And Range("r2:r1048576").Value = "Assignment_Doc" And Range("s2:s1048576").Value = "Not Needed" Then 
        Range("u2:u1048576").Value = "Level 2" 
    ElseIf Range("q2:q1048576").Value = "Document" And Range("r2:r1048576").Value = "Assignment_Doc" And Range("s2:s1048576").Value = "Incorrect Investor" Then 
        Range("u2:u1048576").Value = "Level 1" 
    ElseIf Range("q2:q1048576").Value = "PLX" And Range("r2:r1048576").Value = "Inadequate Research" And Range("s2:s1048576").Value = "Research Not Followed" Then 
        Range("u2:u1048576").Value = "Level 1" 
    ElseIf Range("q2:q1048576").Value = "Document" And Range("r2:r1048576").Value = "Assignment_Doc" And Range("s2:s1048576").Value = "Data Integrity Error" Then 
        Range("u2:u1048576").Value = "Level 1" 
    End If 
End Sub

**vtsoldier2010** · 03-19-2012, 07:51 AM

http://www.ozgrid.com/forum/showthread.php?t=163511, Cross Posted Here:

**Firefly2012** · 03-19-2012, 09:00 AM

Hi

It is because a multicell range returns an array when you access its Value property and you therefore get the type mismatch when your If tries to compare this array to a single string value (such as "PLX").

Would you mind explaining in words what you want your code to do? Something along the lines of "If a V col value is changed then if the V cell = 'PLX' then modify some other cell by..."

**vtsoldier2010** · 03-19-2012, 05:31 PM

[QUOTE=Firefly2012;2737151]Hi

It is because a multicell range returns an array when you access its Value property and you therefore get the type mismatch when your If tries to compare this array to a single string value (such as "PLX").

Would you mind explaining in words what you want your code to do? Something along the lines of "If a V col value is changed then if the V cell = 'PLX' then modify some other cell by..."[/QUOTE

To explain my situation a little better. I have three dependent data validation lists set up in columns Q, R, and S. What I want the code to do is take the choices made in the three data validation lists and compare them to an exact criteria and return a value based on a match to specific criteria. For example if the user chooses "PLX" in column Q and "Imaging_TopCall" in column R and finally "Missing From File" in column S then column U auto populates with "Level 1". A record will be evaluated on every row of the spreadsheet, thus the necessity to have my code run over the entire range of all four columns involved with my code. Is there a way to rewrite my code so that it functions correctly? Any example you can give would be a great help. Thanks so much man>

**tigeravatar** · 03-19-2012, 06:18 PM

vtsoldier2010,

Give this a try. The code assumes that multiple cells in Range("Q2:S" & Rows.Count) can change simultaneously. If it's guaranteed that only 1 cell at a time will change, the code can be greatly simplified:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rngChg As Range
    Dim ChgGrp As Range
    Dim rIndex As Long
    
    Set rngChg = Intersect(Target, Range("Q2:S" & Rows.Count))
    
    If Not rngChg Is Nothing Then
        
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
        
        For Each ChgGrp In rngChg.Areas
            For rIndex = ChgGrp.Row To ChgGrp.Row + ChgGrp.Rows.Count - 1
                Select Case Cells(rIndex, ChgGrp.Column).Column
                    Case 17: Cells(rIndex, ChgGrp.Column + 1).Resize(, 3).ClearContents
                    Case 18: Cells(rIndex, ChgGrp.Column + 1).Resize(, 2).ClearContents
                    Case 19:
                        If Trim(Cells(rIndex, ChgGrp.Column).Value) = vbNullString Then
                            Cells(rIndex, ChgGrp.Column + 1).ClearContents
                        Else
                            Select Case Trim(Cells(rIndex, 17).Value & Cells(rIndex, 18).Value & Cells(rIndex, 19).Value)
                                Case "DocumentAssignment_DocNot Needed":            Cells(rIndex, 20).Value = "Level 2"
                                Case Else:                                          Cells(rIndex, 20).Value = "Level 1"
                            End Select
                        End If
                End Select
            Next rIndex
        Next ChgGrp
        
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
        
    End If
    
End Sub