在while循环中输入类型不匹配错误 [英] Type missmatch error in while loop

问题描述

请有人帮帮我.进入while循环时出现错误.请参见下面的代码.(第一个文件正确运行.但是进入循环时会产生错误)

Somebody please help me. I'm getting error when it enters into while loop. See the below code.(The first file runs correctly. hoever when it enters the loop error will generate)

ActiveSheet.Range("A:E").AutoFilter Field:=1, Criteria1:= _
        "=*CHASE RETURN DATE*", Operator:=xlAnd

完整代码如下:

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 = "|"

    FilesToOpen = Application.GetOpenFilename _
      (FileFilter:="Text Files (*.txt), *.txt", _
      MultiSelect:=True, Title:="Text Files to Open")

    If TypeName(FilesToOpen) = "Boolean" Then
        MsgBox "No Files were selected"
        GoTo ExitHandler
    End If

    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:=xlAnd
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))

    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:=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(2, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 4))
Workbooks(Worksheets(x)).Activate
Selection.AutoFilter
ActiveSheet.Range("A:E").AutoFilter Field:=1, Criteria1:= _
        "=*CHASE RETURN DATE*", Operator:=xlAnd ' This is where I'm getting error as "Type missmatch"
        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

推荐答案

我忘记了在激活工作表之前将变量作为wkball添加.对不起，我的错

I forgot to add variable as wkball before the activation of worksheet. Sorry my mistake

wkbAll.Worksheets(x).Activate

这篇关于在while循环中输入类型不匹配错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！