How do I call a row number by using a variable in VBA?

Question

I recorded a macro, which I want to modify in order to use it automatically in multiple ranges in the Excel worksheet. Here's the code:

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 would like to use the variable "i" to call specific rows, as in the case of Rows(i:i).Select or a range such as Range("E"&i:"AO"&i).Select, but I get an error message: "Expected: list separator or )"

Can you help pls?

Thank you in advance

variables can't be inside double quotes. Non-variables (strings) can't be outside of double quotes. So Range("A"&"i-1":"C"&"i-1") should be Range("A" & i-1 & ":C" & i-1) (as one example). And Range("E"&i:"AO"&i).Select should be Range("E" & i ":AO" & i).Select — JNevill
– JNevill, Commented Oct 26, 2021 at 14:24
Also when using Rows() and only selecting a single row, you don't need the colon. So just Rows(i).Select — JNevill
– JNevill, Commented Oct 26, 2021 at 14:25

JNevill · Accepted Answer · 2021-10-26 14:38:13Z

Besides my comments above, here's a quick rewrite to get rid of all of the superfluous .Activate and .Select lines. Those are for humans, VBA doesn't need to select something before acting on it. It can just act on it directly.

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

And then cleaned up to remove all of that:

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

Collectives™ on Stack Overflow

How do I call a row number by using a variable in VBA?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related