时间戳

首页 > 解决方案 > VBA 运行时错误 9：类似工作表转换为文本文件的下标超出范围

excel - VBA 运行时错误 9：类似工作表转换为文本文件的下标超出范围

问题描述

我已经多次使用这个宏，但没有得到运行时错误 9，今天下标超出范围？工作表的格式似乎相同。关于如何纠正的任何建议？

Sub SaveRowsAsTXT()

Dim wb As Excel.Workbook, wbNew As Excel.Workbook
Dim wsSource As Excel.Worksheet, wsTemp As Excel.Worksheet
Dim r As Long, c As Long
Dim filePath As String
Dim fileName As String
Dim rowRange As Range
Dim cell As Range

filePath = "C:\Users\Me\Desktop\Working\SomeName\"

For Each cell In Range("B1", Range("B10").End(xlUp))
    Set rowRange = Range(cell.Address, Range(cell.Address).End(xlToRight))

    Set wsSource = ThisWorkbook.Worksheets("Sheet1")

    Application.DisplayAlerts = False 'will overwrite existing files without asking

    r = 1
    Do Until Len(Trim(wsSource.Cells(r, 1).Value)) = 0
        ThisWorkbook.Worksheets.Add ThisWorkbook.Worksheets(1)
        Set wsTemp = ThisWorkbook.Worksheets(1)

        For c = 2 To 16
            wsTemp.Cells((c - 1) * 2 - 1, 1).Value = wsSource.Cells(r, c).Value
        Next c
        fileName = filePath & wsSource.Cells(r, 1).Value

        wsTemp.Move
        Set wbNew = ActiveWorkbook
        Set wsTemp = wbNew.Worksheets(1)

        wbNew.SaveAs fileName & ".txt", xlTextWindows 'save as .txt
        wbNew.Close
        ThisWorkbook.Activate
        r = r + 1
    Loop

    Application.DisplayAlerts = True

Next
End Sub

标签： excelvba

解决方案

您可能会收到此错误的原因有很多。可能是您所指的工作表不存在。也可能是您的应用程序在尝试引用其中的某些内容时失去了焦点。为了帮助查明问题，请尝试在运行代码时设置断点以查看究竟是哪一行失败了

推荐阅读