我如何通过在VBA中使用变量来调用行号?

Question

我记录了一个宏，我想修改它，以便在Excel工作表的多个范围内自动使用它。下面是代码:

Sub Macro1()
For i = 6 To 22370 Step 5
ActiveWorkbook.SaveAs Filename:= _
"tute.xlsm" _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Rows(i:i).Select
Range("D" & i).Activate
Selection.Insert Shift:=xlDown
Range("D" & i).Select
ActiveCell.FormulaR1C1 = "xyz"
Range("A"&"i-1":"C"&"i-1").Select
Selection.Copy
Range("A" & i).Select
ActiveSheet.Paste
Range("E" & i).Select
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C*R[4]C"
Range("E" & i).Select
Selection.AutoFill Destination:=Range("E37:AO37"), Type:=xlFillDefault
Range("E"&i:"AO"&i).Select
Range("D" & i).Select
Next
End Sub

我想使用变量" I "调用特定的行，如rows (i:i)的情况。选择或范围，如range ("E"&i:"AO"&i)。选择，但是我得到一个错误消息:"Expected: list separator or)">

你能帮忙吗?

提前谢谢你

Answer 1

除了我上面的评论，这里有一个快速重写，以摆脱所有多余的.Activate和.Select行。那些是为人类，VBA不需要选择的东西之前采取行动。它可以直接作用于它

Sub Macro1()
'This line shouldn't be in your for loop otherwise you save this workbook like 4000 times
ActiveWorkbook.SaveAs Filename:="tute.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
For i = 6 To 22370 Step 5        

'No reason to select the row since we just go ahead and activate a particular cell immediately afterwords        
'Rows(i:i).Select
'No reason to "Activate" the cell. We can just shift it down without highlighting the thing for the user
'Range("D" & i).Activate
Range("D" & i).Insert Shift:=xlDown
'No need to .Select. Just change the formula directly.
'Range("D" & i).Select
Range("D" & i).FormulaR1C1 = "xyz"
'Again, no need to .Select. And we can do the copy/paste in one line
'Range("A"&"i-1":"C"&"i-1").Select
'Selection.Copy
'Range("A" & i).Select
'ActiveSheet.Paste
Range("A" & i-1 & ":C" & i-1).Copy Destination:=Range("A" & i)
'Removing superfluous select again
'Range("E" & i).Select
'Also superfluous code that isn't needed
'Application.CutCopyMode = False
'Application.CutCopyMode = False

Range("E" & i).FormulaR1C1 = "=R[-1]C*R[4]C"
'Range("E" & i).Select
Range("E" & i).AutoFill Destination:=Range("E37:AO37"), Type:=xlFillDefault
'Superfluous selects
'Range("E"&i:"AO"&i).Select
'Range("D" & i).Select
Next
End Sub

然后清理掉所有的

Sub Macro1()

ActiveWorkbook.SaveAs Filename:="tute.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
For i = 6 To 22370 Step 5
'Shift column D for this line down a row (add a new empty cell for this line)      
Range("D" & i).Insert Shift:=xlDown
'Change the value to xyz of column D for this line (the new cell)
Range("D" & i).FormulaR1C1 = "xyz"
'Copy three lines in column A:C and paste 1 line down
Range("A" & i-1 & ":C" & i-1).Copy Destination:=Range("A" & i)

'Change the formula in column E for this line
Range("E" & i).FormulaR1C1 = "=R[-1]C*R[4]C"

'Not sure if this is what you are actually after here. Perhaps that should be `Range("E" & i & ":AO" & i)?`
Range("E" & i).AutoFill Destination:=Range("E37:AO37"), Type:=xlFillDefault
Next
End Sub