Excel VBA Run-time error '13' Type mismatch
Results 1 to 5 of 5

Thread: Excel VBA Run-time error '13' Type mismatch
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2013
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel VBA Run-time error '13' Type mismatch

    i have a excl workbook with sheet1 & mastersheet

    in Master sheet i have defined values in list Column D & F
    Column D has COUNTRY as heading and country Names below it
    Column F has CITY and city list

    Im column A i have 2 data list
    as

    COUNTRY
    CITY

    i have dropdown in excel sheet (SHEET1) in Column K populated with data from MASTER in Column A ie city & country

    When i select city i need Dropdown in column L to be populated with Citylist from MASTERS COLUMN F and if city seleced i need to get data from column D
    but i get type mismatch error
    Code:
    Set wsLists = Worksheets("MasterSheet")
    With Target
             If Target.Value = "COUNTRY" Then
              PartNoRow = Application.Match(.Value, wsLists.Range("COUNTRY"), 0)
              .Offset(0, 1).Value = wsLists.Range("CST")(PartNoRow).Value
            
         
            ElseIf Target.Value = "CITY" Then
               PartNoRow = Application.Match(.Value, wsLists.Range("CITY"), 0)
               .Offset(0, 1).Value = wsLists.Range("CITY")(PartNoRow).Value
            
             End If
          End With
    Last edited by svibuk; Apr 30th, 2014 at 04:53 AM.

  2. #2
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,705
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel VBA Run-time error '13' Type mismatch

    Hi

    It's always much more difficult for members to comment when the full code is not given (so please do post the entire Sub).

    However, you have potential issues around the possible values of Target and whether or not Target comprises a multicellular range.

    You could consider implementing the following change:

    Code:
    Dim temp As String
    Set wsLists = Worksheets("MasterSheet")
    
    temp = Target(1).Text
    
             If temp = "COUNTRY" Then
              PartNoRow = Application.Match(temp wsLists.Range("COUNTRY"), 0)
              Target(1).Offset(0, 1).Value = wsLists.Range("CST")(PartNoRow).Value
            
         
            ElseIf temp = "CITY" Then
               PartNoRow = Application.Match(temp, wsLists.Range("CITY"), 0)
               Target(1).Offset(0, 1).Value = wsLists.Range("CITY")(PartNoRow).Value
            
             End If
    Richard Schollar

    Using xl2013

  3. #3
    New Member
    Join Date
    Nov 2013
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA Run-time error '13' Type mismatch

    Quote Originally Posted by Richard Schollar View Post
    Hi

    It's always much more difficult for members to comment when the full code is not given (so please do post the entire Sub).

    However, you have potential issues around the possible values of Target and whether or not Target comprises a multicellular range.

    You could consider implementing the following change:

    Code:
    Dim temp As String
    Set wsLists = Worksheets("MasterSheet")
    
    temp = Target(1).Text
    
             If temp = "COUNTRY" Then
              PartNoRow = Application.Match(temp wsLists.Range("COUNTRY"), 0)
              Target(1).Offset(0, 1).Value = wsLists.Range("CST")(PartNoRow).Value
            
         
            ElseIf temp = "CITY" Then
               PartNoRow = Application.Match(temp, wsLists.Range("CITY"), 0)
               Target(1).Offset(0, 1).Value = wsLists.Range("CITY")(PartNoRow).Value
            
             End If

    Code:
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim wsLists As Worksheet
    Dim PartNoRow As Long
    Dim PartDescRow As Long
    
    On Error GoTo errHandler
    
    Set wsLists = Worksheets("MasterSheet")
    
    If Target.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    
    Select Case Target.Column
      Case 11
    
     With Target
             If Target.Value = "COUNTRY" Then
              PartNoRow = 1 'Application.Match(.Value, wsLists.Range("COUNTRY"), 0)
              .Offset(0, 1).Value = wsLists.Range("COUNTRY")(PartNoRow).Value
             MsgBox PartNoRow
            
            ElseIf Target.Value = "CITY" Then
             '  PartNoRow = Application.Match("CITY", wsLists.Range("CITY"), 0)
              PartNoRow = 1 'Application.Match(.Value, wsLists.Range("CITY"), 0)
              ' .Offset(0, 1).Value = wsLists.Range("CITY")(PartNoRow).Value
                For Each cPart In wsLists.Range("CITY")
                Target.Offset(0, 3).Value = cPart.Value
                .Offset(0, 1).Value = cPart.Value
                MsgBox (cPart.Value)
                Next cPart
         
             End If
          End With
    
    
    
      Case Else
        'do nothing
    End Select
    
    
    exitHandler:
      Application.EnableEvents = True
      Exit Sub
    errHandler:
      MsgBox Err.Number & ": " & Err.Description
      GoTo exitHandler
    
    End Sub
    full code
    i get error if i use PartNoRow = Application.Match(.Value, wsLists.Range("CITY"), 0)
    i also need to populate the dropdown with the data from list
    but as its not a actual dropdown though i used for loop i get all the calues in the list but the cell doesnot get populated with all data

  4. #4
    New Member
    Join Date
    Nov 2013
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA Run-time error '13' Type mismatch

    i tried the above changed to temp still i get type mismatch error

  5. #5
    New Member
    Join Date
    Dec 2013
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA Run-time error '13' Type mismatch

    can you solve this error

    ERROR SCREEN SHOT https://www.dropbox.com/s/gsuqqxua4p42jo2/error.jpg
    SOURCE FILEhttps://www.dropbox.com/s/ozersrwfmoyzesb/RED.xlsm

    Code:
    Public Sub Part1()
    
    Dim cell As Range
    Dim i As Integer
    
    For Each cell In ActiveSheet.UsedRange
       
       For i = 1 To Len(cell)
          If cell.Characters(i, 1).Font.color = RGB(79, 129, 189) Then
             cell.Characters(i, 1).Font.color = RGB(255, 0, 0)
    
          End If
       Next i
    Next cell
    
    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •