Excel VBA range的变量引用问题
在excel里面编辑了如下代码,可以顺利运行
Sub de()
Dim i As Integer, a As Integer, b As Integer, c As Integer, rn As Range, d As Integer
a = Sheets("Jan").Application.CountA(Range("a:a"))
For i = 1 To 3
Application.Sheets.Add(, Sheets("Jan")).Name = i & "yue"
Sheets("Jan").Range("a1:b1").Copy Sheets(i & "yue").Cells(1, 1)
For Each rn In Sheets("Jan").Range("a1:a10")
If rn = i & "yue" Then
c = Sheets(i & "yue").UsedRange.Rows.Count
Sheets("Jan").Range("a" & rn.Row & ":b" & rn.Row).Copy Sheets(i & "yue").Cells(1, 1).Offset(c)
End If
Next
Next
End Sub
但是修改成下列代码之后(基本只修改了红色的两行),就无法运行(运行到红色的两行excel都会自动提错),但照理来说range的变量引用是可以用cell来代替的啊?为什么无法运行呢?
Sub de()
Dim i As Integer, m As Integer, n As Integer, c As Integer, rn As Range, d As Integer
m = Sheets("Jan").Application.CountA(Range("a:a"))
n = Sheets("Jan").Application.CountA(Range("1:1"))
For i = 1 To 3
Application.Sheets.Add(, Sheets("Jan")).Name = i & "yue"
Sheets("Jan").Range("a1:b1").Copy Sheets(i & "yue").Cells(1, 1)
For Each rn In Sheets("Jan").Range(cells(1,1), cells(m,n))
If rn = i & "yue" Then
c = Sheets(i & "yue").UsedRange.Rows.Count
Sheets("Jan").Range(Cells(c, 1), Cells(c, 2)).Copy Sheets(i & "yue").Cells(1, 1).Offset(c)
End If
Next
Next
End Sub