如何在指定VBA代码中使用已定义变量i调用INDIRECT函数？

Hey there! Let's walk through how you're correctly using the INDIRECT function with your variable i, plus some tweaks to make your code more robust and efficient.

First: Your Current INDIRECT Usage is Correct! #

You've already nailed embedding the variable i into the INDIRECT formula. This line:

Formula1:="=INDIRECT(C" & i & ")"

Since you set i = 7, this will dynamically generate the formula =INDIRECT(C7) for your data validation list. That's exactly what you intended—great job on that part!

Small Fixes & Improvements #

There are a couple of adjustments we can make to clean up the code:

• Variable Declaration: Dim x, i As Integer only declares i as an Integer; x ends up as a Variant. Always explicitly declare all variables:

  Dim x As Integer, i As Integer
  

• Avoid Select/Selection: Using these slows down your code and makes it prone to errors if the user clicks elsewhere while the macro runs. Instead, work directly with range objects.

Revised Code #

Here's the optimized version of your code with these fixes, plus comments to explain each step:

Sub AddValidationWithINDIRECT()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim currentCell As Range
    Dim x As Integer, i As Integer
    
    ' Set the worksheet reference (avoids relying on active sheet)
    Set ws = ThisWorkbook.Sheets("INVOICE")
    ' Set your variable i
    i = 7
    
    ' Find the first empty row in column A
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
    Set currentCell = ws.Cells(lastRow, 1)
    
    ' Loop through x from 1 to 6
    For x = 1 To 6
        If currentCell.Value = "" Then
            ' Assign x to the current cell
            currentCell.Value = x
            ' Add data validation to the adjacent cell (column B)
            With currentCell.Offset(0, 1).Validation
                .Delete ' Clear existing validation first
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                     Formula1:="=INDIRECT(C" & i & ")"
                .IgnoreBlank = True
                .InCellDropdown = True
                .ShowInput = True
                .ShowError = True
            End With
            ' Move to the next row in column A
            lastRow = lastRow + 1
            Set currentCell = ws.Cells(lastRow, 1)
        End If
    Next x
End Sub

Key Changes Explained #

• We use a worksheet variable ws to directly reference the "INVOICE" sheet, so the code doesn't depend on which sheet is active.
• We work with currentCell instead of selecting cells, making the code faster and more reliable.
• Explicit variable declarations prevent unexpected behavior from variant types.

内容的提问来源于stack exchange，提问作者Sajid Mohammad