Question

Excel VBA 下标越界

Sub value_input() ' 添加转换数据到sheet2
x = InputBox("总共有多少行数据？")
For i = 1 To x
ThisWorkbook.Worksheets("sheet2").Cells(i, 1) = Application.WorksheetFunction.Rept(0, 10 - Len(ThisWorkbook.Worksheets("sheet1").Cells(i, 1))) & ThisWorkbook.Worksheets("sheet1").Cells(i, 1)
ThisWorkbook.Worksheets("sheet2").Cells(i, 2) = ThisWorkbook.Worksheets("sheet1").Cells(i, 2)
ThisWorkbook.Worksheets("sheet2").Cells(i, 3) = Application.WorksheetFunction.Text(Hour(ThisWorkbook.Worksheets("sheet1").Cells(i, 3)), "00") & ":" & Application.WorksheetFunction.Text(Minute(ThisWorkbook.Worksheets("sheet1").Cells(i, 3)), "00") & ":" & "00"
Next i
End Sub

总是报下标越界，请告知是何原因？

Answer 1

在我机上问题xp+exl2003
如果你那还是有问题
用下面代码试试
Sub value_input() ' 添加转换数据到sheet2
x = InputBox("总共有多少行数据？")
For i = 1 To x
Sheets("sheet2").Cells(i, 1) = Right("0000000000" & Sheets("sheet1").Cells(i, 1), 10)
Sheets("sheet2").Cells(i, 2) = Sheets("Sheet1").Cells(i, 2)
Sheets("sheet2").Cells(i, 3) = Format(Sheets("sheet1").Cells(i, 3), "HH:MM:SS")
Next i
End Sub

追问

用了你写的代码，但很奇怪还是报下标越界，我是win7+excel2010

追答

"Sheet1" 再对一下工作表名称,看有没有没错
我不是win7+excel2010的,不能在win7+excel2010测试

Answer 2

检查要执行代码的工作表标签是否是是sheet1；sheet2.如果不是改成对应的工作表标签。