|
请老师看看这段代码在win10 excel2016 中报错,但是在win7中正常使用
错误提示:object variable or with block variable not set
Option Explicit
Sub LN_Format_Click()
Dim varColID As Variant, lngStartRow As Long, strSplitChar As String, lngGetID As Long
Dim lngCol As Long, strColID As String
Dim strChar As String, lngID As Long, lngLen As Long
Dim rgTop As Range, lngRows As Long
Dim arrData As Variant, strSplit() As String
'这些参数,你也可以自己灵活设定
'代码只提供了 列号或列符的 输入
lngStartRow = 2 '数据起始行
strSplitChar = "." '分割符
lngGetID = 0 '提取索引
varColID = Application.InputBox("请输入列号或列符", "指定列")
strColID = UCase(Trim(varColID))
If varColID = False Or strColID = "" Then Exit Sub '无输入或输入为空
strColID = Replace(strColID, Space(1), "")
If IsNumeric(varColID) Then
lngCol = Val(varColID)
Else
lngLen = Len(strColID)
For lngID = 1 To lngLen
strChar = Mid(strColID, lngID, 1)
If Asc(strChar) < 65 Or Asc(strChar) > 90 Then Exit Sub '输入的列符不在[A-Z]中
lngCol = lngCol + (Asc(strChar) - 64) * (26 ^ (lngLen - lngID))
Next
End If
If lngCol < 1 Or lngCol > Columns.Count Then Exit Sub '列号为0或大于最大列号
Set rgTop = ActiveSheet.Cells(1, lngCol).set
lngRows = ActiveSheet.Cells(Rows.Count, lngCol).End(xlUp).Row
If lngRows < lngStartRow Then lngRows = lngStartRow
arrData = rgTop.Resize(lngRows, 1)
For lngID = lngStartRow To UBound(arrData)
strSplit = Split(arrData(lngID, 1), strSplitChar)
If UBound(strSplit) >= lngGetID Then arrData(lngID, 1) = strSplit(lngGetID)
Next
rgTop.Resize(lngRows, 1) = arrData
End Sub
|
|