Organizational Research By

Surprising Reserch Topic

Experts Most Trusted Topic


type mismatch error in filter excel vba using -'excel,vba,excel-vba'


type mismatch error in filter excel vba  using -'excel,vba,excel-vba'

I have a list of text files which I'll import the data to new excel files in each tab accordance with the files imported. (say 20 text file = 20 tabs)
1. The first tab will delimited the data as per my requirement
2. Then it will filter as per the my 1st criteria and paste the data in a specified file.
3.It will again filter 2nd criteria and paste in the same way.
4. The first tab it will run correctly

Now I replicated for the rest of the tabs (Using while)however, the 1st criteria will run correctly but the 2nd criteria I'm getting the error as "Type Mismatch"
I have given a big space and comment where I'm getting error

Option Explicit

Sub CombineTextFiles()
    Dim FilesToOpen
    Dim x As Integer
    Dim wkbAll As Workbook
    Dim wkbTemp As Workbook
    Dim sDelimiter As String
    Dim erow
    Dim IRow As Long

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    sDelimiter = "|"
'Import multiple Text files
    FilesToOpen = Application.GetOpenFilename _
      (FileFilter:="Text Files (*.txt), *.txt", _
      MultiSelect:=True, Title:="Text Files to Open")
'Only if none get selected
    If TypeName(FilesToOpen) = "Boolean" Then
        MsgBox "No Files were selected"
        GoTo ExitHandler
    End If
'Create new tabs to generate one file with delimited
    x = 1
    Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
    wkbTemp.Sheets(1).Copy
    Set wkbAll = ActiveWorkbook
    wkbTemp.Close (False)
    wkbAll.Worksheets(x).Columns("A:A").TextToColumns _
      Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(47, 2), Array(72, 2), Array(93, 2), Array(103, 2)) _
        , TrailingMinusNumbers:=True
        Cells.Select
        Selection.AutoFilter
        ActiveSheet.Range("A:E").AutoFilter Field:=2, Criteria1:="=*$*", _
        Operator:=xlAnd
        ActiveSheet.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy
    Workbooks("Test.xlsm").Activate
        Sheets("Sheet1").Select
        erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 4))

'To pick the date
wkbAll.Worksheets(x).Activate
    Selection.AutoFilter
    ActiveSheet.Range("A:E").AutoFilter Field:=1, Criteria1:= _
        "=*CHASE RETURN DATE*", Operator:=xlFilterValues
            With ActiveSheet.UsedRange.Columns(4).Offset(1, 0).Resize(Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible).Select
            End With
            Selection.Copy
 Workbooks("Test.xlsm").Activate
    Sheets("Sheet1").Select
    erow = Sheet1.Cells(Rows.Count, 6).End(xlUp).Offset(1, 0).Row
    ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 6), Cells(erow, 6))

'Sum Amount
wkbAll.Worksheets(x).Activate
    Selection.AutoFilter
    ActiveSheet.Range("A:E").AutoFilter Field:=3, Criteria1:= _
        "=*$*", Operator:=xlAnd
            With ActiveSheet.UsedRange.Columns(3).Offset(1, 0).Resize(Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible).Select
            End With
            Selection.Copy
Workbooks("Test.xlsm").Activate
    Sheets("Sheet1").Select
    erow = Sheet1.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
    ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 2), Cells(erow, 2))

'Create new tabs to generate rest of the files with delimited, filter, criteria as above
    x = x + 1

    While x <= UBound(FilesToOpen)
        Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
        With wkbAll
            wkbTemp.Sheets(1).Move After:=.Sheets(.Sheets.Count)
            .Worksheets(x).Columns("A:A").TextToColumns _
              Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(47, 2), Array(72, 2), Array(93, 2), Array(103, 2)) _
        , TrailingMinusNumbers:=True
        Cells.Select
    Selection.AutoFilter
    ActiveSheet.Range("A:E").AutoFilter Field:=2, Criteria1:="=*$*", _
        Operator:=xlFilterValues
        ActiveSheet.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy
Workbooks("Test.xlsm").Activate
    Sheets("Sheet1").Select
    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(2, 0).Row
    ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 4))
'To pick the date
Workbooks(Worksheets(x)).Activate
        Selection.AutoFilter


'The below code I'm getting the error as Type mismatch
ActiveSheet.Range("A:E").AutoFilter Field:=1, Criteria1:= _
            "=CHASE RETURN DATE", Operator:=xlFilterValues

        End With
        x = x + 1
    Wend

ExitHandler:
    Application.ScreenUpdating = True
    Set wkbAll = Nothing
    Set wkbTemp = Nothing
    Exit Sub

ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub

    

asked Sep 7, 2015 by rajesh
0 votes
23 views



Related Hot Questions



Government Jobs Opening


...