VBA Run time error 13: Mismatch

[kamisha05]

Hello,

I am using the following subroutine and recently I have received a run time error stating type mismatch in regards to the line: Sep = Chr(9). I never had an issue with that line in the past. Any ideas?

Sub DoTheImport()


Dim shtName As String
Dim Chr
shtName = "Product " & CStr(ProdNum)

Dim Prod As Variant
Dim Sep As String

Application.ScreenUpdating = False


'Activate data sheet
Worksheets(shtName).Activate
Worksheets(shtName).Cells(1, 1).Activate


'Ask for file
Prod = Application.GetOpenFilename(FileFilter:="Input Files (*.csv),*csv")

'If no file selected, end macro
If Prod = False Then
Exit Sub
End If

Sep = Chr(9)
ImportProduct FName:=CStr(Prod), Sep:=Sep


End Sub

 

[Kenneth Hobson]

Welcome to the forum! Please paste code between code tags. Insert them by click the # icon on the reply message toolbar.

Use

Option Explicit

as the first line in a Module.

You could then see that you missed Dimming some variable. Do not use variable names where a command name already exists like Chr.

Sub DoTheImport()
  Dim shtName As String, Prod As Variant, Sep As String
  Dim ProdNum As Long
  
  ProdNum = 123
  shtName = "Product " & CStr(ProdNum)
  
  Application.ScreenUpdating = False
  
  'Activate data sheet
  Worksheets(shtName).Activate
  Worksheets(shtName).Cells(1, 1).Activate
  
  'Ask for file
  Prod = Application.GetOpenFilename(FileFilter:="Input Files (*.csv),*csv")
  
  'If no file selected, end macro
  If Prod = False Then Exit Sub
  
  Sep = Chr(9)
  'ImportProduct FName:=CStr(Prod), Sep:=Sep
End Sub

 

[kamisha05]

Sub DoTheImport(ProdNum)

Dim shtName As String
Dim Chr
    shtName = "Product " & CStr(ProdNum)
    
    Dim Prod As Variant
    Dim Sep As String
    
Application.ScreenUpdating = False


'Activate data sheet
    Worksheets(shtName).Activate
    Worksheets(shtName).Cells(1, 1).Activate
    
    
'Ask for file
    Prod = Application.GetOpenFilename(FileFilter:="Input Files (*.csv),*csv")
    
'If no file selected, end macro
    If Prod = False Then
        Exit Sub
    End If
    
    Sep = Chr(9)
    ImportProduct FName:=CStr(Prod), Sep:=Sep
    
    
End Sub

 

[kamisha05]

Thank you for the insight. I have reposted the code within the # limits.

 

[Kenneth Hobson]

You still have Dim Chr. Delete that line.

 

[kamisha05]

Also, I have ProdNum defined already in a public subroutine located in a different module within the workbook.

 

[kamisha05]

Deleting that line solved the problem! Thank you so much.