如何使用VBA中的變數呼叫行號？

我錄制了一個宏，我想對其進行修改，以便在 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).Select或Range("E"&i:"AO"&i).Select 等范圍，但我收到錯誤訊息：“預期：串列分隔符或）”

你能幫忙嗎？

先感謝您

uj5u.com熱心網友回復：

除了我上面的評論，這里有一個快速重寫，以擺脫所有多余的.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

標籤：擅长 vba

上一篇：回傳從單元格到其下方最后一個非空單元格的溢位范圍

下一篇：Excel超鏈接不起作用“無法打開指定的檔案”