Run-time error '13': Type Mismatch - but I can't see it. :(

[a15457]

Hi All,

I've written a VBA Macro that basically pulls data from closed excel workbooks. It was previously working OK but then I broke it somehow the other day when I was trying to add in some error checking.

The code is below and it breaks at the point where the text is RED. The error is the one from the title of the thread but I can't work out where my mismatch is or what I've done to break it.

Anything in "Italics" is me sanitizing the code so I can publish it on the web.

I really need to get this fixed asap so all your help is appreciated.

Sub Importdata()
Dim AreaAddress, Path, Filename, FullFilename As String
Dim ColCount, ColInc, FileInc, RowInc, a, b, c, d As Long
Dim WSCount As Integer
Dim mainWB As Workbook
Set mainWB = ActiveWorkbook
WSCount = mainWB.Sheets.Count
Dim RowList As Variant
RowList = Array(4, 6, 7, 8, 9, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94)
Dim TargetList As Variant
TargetList = Array(7, 8, 9, 10, 11, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34)
Dim WaveList() As String
ReDim WaveList(1 To WSCount)


Application.AutoCorrect.AutoFillFormulasInLists = False

For b = 1 To WSCount
    WaveList(b) = mainWB.Sheets(b).Name
Next

Path = "[I]Filepath[/I]\SERVERS\"

For Each WaveElement In WaveList

    If WaveElement <> "SampleWave" Then
        Worksheets(WaveElement).Activate
        ColCount = ActiveSheet.Cells(3, Columns.Count).End(xlToLeft).Column - 1
        
        For a = 1 To ColCount
        
                Filename = Sheets(WaveElement).Cells(3, a + 1)
                FullFilename = ""
                FullFilename = Path & Filename & "\[I]clientname[/I]-" & Filename & ".xlsx"
                If Dir(FullFilename) <> "" Then
                
                    For d = 0 To 4
                    
[COLOR="#FF0000"]                        If Sheets(WaveElement).Cells(RowList(d), a + 1) = "" Then[/COLOR]
                            Sheets(WaveElement).Cells(RowList(d), a + 1) = "= '" & Path & Filename & "\" & "[[I]clientname[/I]-" & Filename & ".xlsx]Prework Checklist'!$C$" & TargetList(d)
                        Else
                            Sheets(WaveElement).Cells(RowList(d), a + 1).ClearContents
                            Sheets(WaveElement).Cells(RowList(d), a + 1) = "= '" & Path & Filename & "\" & "[[I]clientname[/I]-" & Filename & ".xlsx]Prework Checklist'!$C$" & TargetList(d)
                        End If
                    
                    Next
        ' Prework Checks
                    For d = 5 To 40
                        If Sheets(WaveElement).Cells(RowList(d), a + 1) = "" Then
                            Sheets(WaveElement).Cells(RowList(d), a + 1) = "= '" & Path & Filename & "\" & "[[I]clientname[/I]-" & Filename & ".xlsx]Prework Checklist'!$J$" & TargetList(d)
                        Else
                            Sheets(WaveElement).Cells(RowList(d), a + 1).ClearContents
                            Sheets(WaveElement).Cells(RowList(d), a + 1) = "= '" & Path & Filename & "\" & "[[I]clientname[/I]-" & Filename & ".xlsx]Prework Checklist'!$J$" & TargetList(d)
                        End If
                    Next
        
        ' Migration Checks
                    For d = 41 To 60
                        If Sheets(WaveElement).Cells(RowList(d), a + 1) = "" Then
                            Sheets(WaveElement).Cells(RowList(d), a + 1) = "= '" & Path & Filename & "\" & "[[I]clientname[/I]-" & Filename & ".xlsx]Migration Checklist'!$J$" & TargetList(d)
                        Else
                            Sheets(WaveElement).Cells(RowList(d), a + 1).ClearContents
                            Sheets(WaveElement).Cells(RowList(d), a + 1) = "= '" & Path & Filename & "\" & "[[I]clientname[/I]-" & Filename & ".xlsx]Migration Checklist'!$J$" & TargetList(d)
                        End If
                    Next
        
        ' Onboarding Checks
                    For d = 61 To 81
                        If Sheets(WaveElement).Cells(RowList(d), a + 1) = "" Then
                            Sheets(WaveElement).Cells(RowList(d), a + 1) = "= '" & Path & Filename & "\" & "[[I]clientname[/I]-" & Filename & ".xlsx]Onboarding Checklist'!$J$" & TargetList(d)
                        Else
                            Sheets(WaveElement).Cells(RowList(d), a + 1).ClearContents
                            Sheets(WaveElement).Cells(RowList(d), a + 1) = "= '" & Path & Filename & "\" & "[[I]clientname[/I]-" & Filename & ".xlsx]Onboarding Checklist'!$J$" & TargetList(d)
                        End If
                    Next
                End If
        Next
    End If
    
Next WaveElement

Worksheets(WaveList(1)).Activate

End Sub

Kind regards,

Dave

 

[sericom]

Do you need that if block? Looks like you're putting the same formula in there on both paths.


All this:

[COLOR=#FF0000]                        If Sheets(WaveElement).Cells(RowList(d), a + 1) = "" Then[/COLOR]
                            Sheets(WaveElement).Cells(RowList(d), a + 1) = "= '" & Path & Filename & "\" & "[[I]clientname[/I]-" & Filename & ".xlsx]Prework Checklist'!$C$" & TargetList(d)
                        Else
                            Sheets(WaveElement).Cells(RowList(d), a + 1).ClearContents
                            Sheets(WaveElement).Cells(RowList(d), a + 1) = "= '" & Path & Filename & "\" & "[[I]clientname[/I]-" & Filename & ".xlsx]Prework Checklist'!$C$" & TargetList(d)
                        End If

should be able to be replaced with just

Sheets(WaveElement).Cells(RowList(d), a + 1) = "= '" & Path & Filename & "\" & "[[I]clientname[/I]-" & Filename & ".xlsx]Prework Checklist'!$C$" & TargetList(d)

 

[a15457]

Yep that did the trick. I wrote this so long ago I can't even remember why I put the IFs in there.

Thanks heaps.

See just needed fresh eyes.

Cheers,

Dave

:D

 

[MARK858]

@a15457, I think you probably should also look at the way you are declaring your variables.
In VBA you need to explicitly declare your variables individually.

At the moment the below variables

Dim AreaAddress, Path, Filename, FullFilename As String
Dim ColCount, ColInc, FileInc, RowInc, a, b, c, d As Long

are being declared as

Variants
AreaAddress, Path, Filename, ColCount, ColInc, FileInc, RowInc, a, b, c

String
FullFilename

Long
d

which is probably not what you intended.

 

[a15457]

Thanks for the tip Mark858.

:D