Excel+VBA: 应用程序定义或对象定义错误
出错信息: Excel+VBA: 应用程序定义或对象定义错误
Excel 版本2003
运行以下代码出错:(出错代码行已标为黄色)
Private Sub CommandButton1_Click()
Dim namedRange As Range, tmpRange As Range
Dim findAddress As String
km = "政治语文数学物理化学生物历史地理英语音乐美术信息技术通用技术"
With ThisWorkbook
For i = 1 To .Sheets.Count
If InStr(km, .Sheets(i).Name) > 0 Then
MsgBox (.Sheets(i).Name)
Set namedRange = Nothing
'Set namedRange = .Sheets(i).Range([a1], [a100])
.Sheets(i).Activate
Set namedRange = .Sheets(i).Range([a1], [a100]) Set tmpRange = namedRange.Find(What:="2310832007010007")
If tmpRange Is Nothing Then
MsgBox ("Not found!")
End
Else
MsgBox ("Found!")
End If
x = 4
Do
MsgBox (.Sheets(i).Cells(2, x))
MsgBox (.Sheets(i).Cells(tmpRange.Row, x))
MsgBox (.Sheets(i).Cells(tmpRange.Row, x + 1))
x = x + 3
Loop Until .Sheets(i).Cells(tmpRange.Row, x) = ""
End If
Next
End With
End Sub
[解决办法]
先不要用级联表达式，这样改改
dim aSheet as WorkSheet
set aSheet = .Sheets(i)
set namedRange = aSheet.Range("a1", "a100")
