我正在尝试创建excel模板(数据量因情况而异),看起来像这样:
在每偶数行中都是“客户”,我想在每奇数行中都输入“账本”。基本上,应该将“ Ledger”放入每个奇数行,直到C列中有数据为止。我有以下代码:
'========================================================================
' INSERTING LEDGERS for every odd row (below Customer)
'========================================================================
Sub Ledgers()
Dim rng As Range
Dim r As Range
Dim LastRow As Long
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
Set rng = .Range("C5:C" & LastRow)
For i = 1 To rng.Rows.Count
Set r = rng.Cells(i, -2)
If i Mod 2 = 1 Then
r.Value = "Ledger"
End If
Next i
End Sub
但是它给我一个错误msg无效或不合格的引用。您能告诉我我哪里有错误吗?
非常感谢!
如果一个命令开始.
喜欢.Cells
它希望成为一个内with
像的语句...
With Worksheets("MySheetName")
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
Set rng = .Range("C5:C" & LastRow)
End With
因此,您需要指定期望单元格所在的工作表的名称。
并不是最好Option Explicit
在模块顶部使用它来强制声明每个变量(您未声明i As Long
)。
您的代码可以简化为...
Option Explicit
Public Sub Ledgers()
Dim LastRow As Long
Dim i As Long
With Worksheets("MySheetName")
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
'make sure i starts with a odd number
'here we start at row 5 and loop to the last row
'step 2 makes it overstep the even numbers if you start with an odd i
'so there is no need to proof for even/odd
For i = 5 To LastRow Step 2
.Cells(i, "A") = "Ledger" 'In column A
'^ this references the worksheet of the with-statement because it starts with a `.`
Next i
End With
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句