下标超出VBA范围错误

I am trying to import FDF files(can be multiple) with VBA. When I run my code I got Subscript out of range error.

我正在尝试使用VBA导入FDF文件(可以是多个)。当我运行我的代码时,我的Subscript超出了范围错误。

I know that the error suggests the worksheet it is looking for does not exist but I don't believe the code below actually defines the worksheet name which is probably the cause of the problem?

我知道错误表明它正在寻找的工作表不存在,但我不相信下面的代码实际上定义了可能导致问题的工作表名称?

Can I have assistance in where, and what, code to insert to address this error? This is my code what I tried:

我可以帮助您在何处以及为了解决此错误而插入什么代码?这是我尝试的代码:

Sub FDFImport()

    Dim OutSH As Worksheet
    Dim Fname As Variant, f As Integer
    Fname = Application.GetOpenFilename("FDF File,*.fdf", 1, "Select One Or More Files To Open", , True)

    For f = 1 To UBound(Fname)

      Open Fname(f) For Input As #1

      Do While Not EOF(1)

         Line Input #1, myvar
         arr = Split(myvar, Chr(10))
         arr2 = Split(arr(4), "/V")

         If InStr(1, myvar, "(Contact)") > 0 Then

            Set OutSH = Sheets("Contact")
            outrow = OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

            For i = 1 To 8

               placer = InStr(1, arr2(i), ")")
               OutSH.Cells(outrow, i).Value = Left(arr2(i), placer - 1)

            Next i

         Else

            Set OutSH = Sheets("NoContact")
            outrow = OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

            For i = 1 To 12

               placer = InStr(1, arr2(i), ")")
               OutSH.Cells(outrow, i).Value = Left(arr2(i), placer - 1)

            Next i

         End If

      Loop

      Close #1

      Sheets("Contact").Cells.Replace what:="(", replacement:=""

      Sheets("NoContact").Cells.Replace what:="(", replacement:=""

   Next f

End Sub

1 个解决方案

#1

This is just a guess based on what you have posted but give this a try

这只是基于您发布的内容的猜测,但尝试一下

Sub FDFImport()

    Dim OutSH As Worksheet


    Dim Fname As Variant, f As Integer
    Dim myvar, arr, arr2, outrow, i, placer

    Fname = Application.GetOpenFilename("FDF File,*.fdf", 1, "Select One Or More Files To Open", , True)

    If VarType(Fname) = vbBoolean Then
        Exit Sub
    End If

    For f = LBound(Fname) To UBound(Fname)

      Open Fname(f) For Input As #1

      Do While Not EOF(1)

         Line Input #1, myvar

         arr = Split(myvar, Chr(10))

         arr2 = Split(arr(4), "/V")

         If InStr(1, myvar, "(Contact)") > 0 Then

            Set OutSH = Sheets("Contact")
            outrow = OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

            For i = 0 To 7

               placer = InStr(1, arr2(i), ")")
               OutSH.Cells(outrow, i).Value = Left(arr2(i), placer - 1)

            Next i

         Else

            Set OutSH = Sheets("NoContact")
            outrow = OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

            For i = 0 To 11

               placer = InStr(1, arr2(i), ")")
               OutSH.Cells(outrow, i).Value = Left(arr2(i), placer - 1)

            Next i

         End If

      Loop

      Close #1

      Sheets("Contact").Cells.Replace what:="(", replacement:=""

      Sheets("NoContact").Cells.Replace what:="(", replacement:=""

   Next f

End Sub

When you Split the array will be 0 based. Meaning you need loop through the array from 0 to X. When you are looping arr2 you have For i = 1 To 8 my guess is it should be For i = 0 To 7 you are doing the same for arr I have changed this is my answer.

拆分时,数组将基于0。意思是你需要循环遍历从0到X的数组。当你循环arr2时,你有For i = 1到8我的猜测应该是对于i = 0到7你为arr做同样的事情我已经改变了这是我的回答。