Excel 2010双变量运行时错误16

Question

我们在办公室的一台用户计算机上运行此代码时遇到问题 - 所有其他用户计算机都运行良好(Windows XP 操作系统、Excel 2010 Standard 或 Professional) - 这台计算机是 Windows XP，运行 Excel 2010 Professional。运行时错误 16 出现在标记的行上 --> 问题似乎是变量 i - 突出显示提示显示 i = -1.#IND

Sub FormatSheet(strResultSheet As String)
    Dim oCol As Excel.Range
    Dim i As Double
    Dim R As String
    Dim iColumn As Integer

    ' Special rountine to convert text column into numeric
    Sheets(strResultSheet).Select
    iColumn = 0
--> For i = 1 To Worksheets(strResultSheet).Cells.SpecialCells(xlLastCell).Column
        If UCase(Cells(1, i).Text) = "QUANTITY" Then
            iColumn = i
            Exit For
        End If
    Next
    Sheets(strResultSheet).Select
    If iColumn > 0 Then
        Columns(iColumn).Select
        Selection.NumberFormat = "#,##0.00"
        Selection.HorizontalAlignment = xlHAlignRight
        For i = 2 To Sheets(strResultSheet).Cells.SpecialCells(xlLastCell).Row
            If Cells(i, iColumn).Text <> "" Then
                Cells(i, iColumn).Value = Cells(i, iColumn).Value * 1
            End If
        Next
    End If

End Sub

有人知道我们需要做什么来修复用户机器来处理吗？宏嵌入在第三方日常电子邮件中，因此无法调整代码来修复。

Answer 1

尝试下面的代码:

Sub FormatSheet(strResultSheet As String)

    Dim rng As Range, lastRow As Integer

    With Sheets(strResultSheet)
        .Select
        Set rng = .Rows("1:1").Find("QUANTITY")

        If Not rng Is Nothing Then
            rng.EntireColumn.NumberFormat = "#,##0.00"
            rng.HorizontalAlignment = xlHAlignRight

            lastRow = Cells(65000, rng.Column).End(xlUp).Row
            For i = 2 To lastRow
                Cells(i, rng.Column).Value = Cells(i, rng.Column).Value * 1
            Next
        End If
    End With

End Sub