Compile error: Type Mismatch

[EdwardL95]

Hi All,

When trying to put an array in a IF = False then statement it is giving me a "Compile error Type Mismatch" error. Does anyone have an Idea?, I can get around this by removing the If statement or array...

Code below:

Dim Viia7Files() As Variant
Dim wb As Workbook
Dim wb2 As Workbook
Dim I As Integer

Set wb = ThisWorkbook
    
Viia7Files = Application.GetOpenFilename("Excel Files (*.xls*),*.xl*", , "Choose File", "Open", True)



If Viia7Files = False Then Exit Sub
    
Else
    
    For I = 1 To Application.CountA(Viia7Files)

    Set wb2 = Workbooks.Open(Viia7Files(I))
    
End If

Thanks,
Ed

 

[jasonb75]

Try explaining what you're trying to do, there are too many errors in your code for anyone to make sense of it.

 

[GWteB]

Just guessing ...

Sub EdwardL95()

    Dim Viia7Files As Variant
    Dim wb As Workbook
    Dim wb2 As Workbook
    Dim I As Long

    Set wb = ThisWorkbook
    
    Viia7Files = Application.GetOpenFilename("Excel Files (*.xls*),*.xl*", , "Choose File", "Open", True)

    If VarType(Viia7Files) = vbBoolean Then
        Exit Sub
    
    Else
        For I = 1 To UBound(Viia7Files)
            Set wb2 = Workbooks.Open(Viia7Files(I))
        Next I
    End If
End Sub

 

[EdwardL95]

Try explaining what you're trying to do, there are too many errors in your code for anyone to make sense of it.

Apologies I'm still new to this.

What I wanted to do was add in the section below however I was getting the mismatach error and If I were to remove it the code works.

If Viia7Files = False Then

MsgBox ("No File Selected")
    
    Else

Full code is below:

Sub ImportViia7File()

Dim Viia7Files() As Variant
Dim wb As Workbook
Dim wb2 As Workbook
Dim I As Long

Set wb = ThisWorkbook
    
Viia7Files = Application.GetOpenFilename("Excel Files (*.xls*),*.xl*", , "Choose File", "Open", True)


If Viia7Files = False Then

MsgBox ("No File Selected")
    
    Else
    
End If

For I = 1 To Application.CountA(Viia7Files)

    Set wb2 = Workbooks.Open(Viia7Files(I))
    

wb.Activate
wb.Worksheets.Add(Before:=Sheets("Worklist_Template")).Name = "Viia7Data_" & I
    
wb2.Activate
wb2.Sheets("Results").Range("D43:E1195").Copy wb.Sheets("Viia7Data_" & I).Range("A1")
wb2.Sheets("Results").Range("G43:G1195").Copy wb.Sheets("Viia7Data_" & I).Range("C1")
wb2.Sheets("Results").Range("I43:I1195").Copy wb.Sheets("Viia7Data_" & I).Range("D1")

Application.CutCopyMode = False
wb2.Close

wb.Activate

Next I


End Sub

 

[GWteB]

With this line of code

Dim Viia7Files() As Variant

you're explicitly declaring a variable of the Variant/Array vartype. Remove the parenthesis, then it would be a generic Variant vartype and can then take any type.
The GetOpenFileName function returns either an Array vartype (one ore multiple files selected by user) or a Boolean vartype (cancel pressed).

 

[EdwardL95]

With this line of code

Dim Viia7Files() As Variant

you're explicitly declaring a variable of the Variant/Array vartype. Remove the parenthesis, then it would be a generic Variant vartype and can then take any type.
The GetOpenFileName function returns either an Array vartype (one ore multiple files selected by user) or a Boolean vartype (cancel pressed).

Thank you.

 

[GWteB]

My pleasure