excel - 查找单元格，粘贴并格式化时间值

Question

我想在文本框中输入 3 或 4 位数的值，验证它是否为数字，将数字拆分为 1 或 2 位数的分钟数和 2 位数的秒数。然后，我想在电子表格上找到一个命名范围，移动到该范围的右端，然后将时间值从该单元格向下粘贴多个单元格。我还想将要粘贴的单元格格式化为分钟和秒单元格。其中大部分工作，但我在小程序的最后遇到了偏移粘贴和格式的问题。
现在的问题是，将 TextBox2 值粘贴到该单元格中。

Dim tbV As String
Dim sV As String
Dim mV As String
Dim TimeCell As Range
Dim LastColl As Range

tbV = TextBox2.Text

    If Len(tbV) > 4 Or Len(tbV) < 2 Or Not IsNumeric(tbV) Then
        MsgBox "wrong"
    Exit Sub
    End If


sV = Right(tbV, 2)
mV = Left(tbV, Len(tbV) - 2)


Dim iOffset As Integer
    Select Case Range("AC1").Value
        Case Is = 30: iOffset = 16
        Case Is = 33: iOffset = 20
        Case Is = 22: iOffset = 14
    End Select

Set LastColl = Range("Battery" & BatteryNumber).End(xlToRight)

Set TimeCell = Range(LastCol.Offset(iOffset, 0))

TimeCell.NumberFormat = "m:ss"
TimeCell.Value = TimeSerial(0, Val(mV), Val(sV))

Answer 1

替换这一行:

Set LastColl = Range("Battery" & BatteryNumber).End(xlToRight)

有了这个:

Dim Rng As Range
 Set Rng = Range("Battery" & BatteryNumber)
 Set LastColl = Rng(Rng.Columns.Count)