使用VBA宏转换Excel日期列遇格式报错及动态范围适配问题

问题1：设置单元格格式抛出异常的原因 #

核心原因为单元格引用逻辑错误：

• 你已将rng定义为Sheet2!E2:E100，该范围本身仅包含1列（对应工作表的E列）。调用rng.Cells属性时，行、列索引均为该范围的相对位置，而非整个工作表的绝对坐标。
• 代码中指定ColumnIndex:="E"等价于列索引值5，超出了rng的列数上限（仅1列），触发索引越界异常。
• 次要可能原因：若Sheet2处于保护状态、E列单元格被锁定，也会导致修改NumberFormat属性时抛出权限异常，可先检查工作表保护设置。

问题2：适配E列动态有效行数的实现方案 #

可以通过End(xlUp)方法动态获取E列最后一个有数据的行号，无需硬编码范围上限。推荐优先使用批量处理方案，避免遍历带来的性能损耗，代码如下：

Dim ws As Worksheet
Dim lastRow As Long
Dim targetRng As Range

' 绑定目标工作表
Set ws = ThisWorkbook.Worksheets("Sheet2")
' 动态获取E列最后一个有数据的行号
lastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row

' 仅当E列存在表头外的有效数据时执行操作
If lastRow >= 2 Then
    Set targetRng = ws.Range("E2:E" & lastRow)
    ' 批量设置单元格为文本格式
    targetRng.NumberFormat = "@"
    ' 批量转换为24小时制HH:MM格式的字符串
    targetRng.Value = ws.Evaluate("=TEXT(E2:E" & lastRow & ",""HH:MM"")")
End If

如果需要保留遍历写法，修正后的代码如下：

Dim dt As Date
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long

Set ws = ThisWorkbook.Worksheets("Sheet2")
lastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row

For i = 2 To lastRow
    dt = ws.Cells(i, "E").Value
    ws.Cells(i, "E").NumberFormat = "@"
    ws.Cells(i, "E").Value = Format(dt, "HH:MM")
Next

内容的提问来源于stack exchange，提问作者DDulla