Excel VBA Run-time error '13' Type mismatch

svibuk

New Member
Joined
Nov 27, 2013
Messages
7
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:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
 
Upvote 0
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
 
Upvote 0
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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