Excel VBA固定列+变量行实现及Run-time error 1004后续咨询

Got it, let's break down how to insert your myRow variable into that fixed-column formula string you're working with. The key here is properly concatenating strings in VBA, especially handling the double quotes needed for the formula logic.

Core Concept: String Concatenation with Escaped Quotes #

In Excel formulas, you use double quotes to wrap text values (like "Y"). But in VBA, double quotes inside a string need to be escaped by writing two double quotes to represent a single one in the final formula.

To replace the fixed row number (like 13 in $BA$13) with your myRow variable, you'll use the & operator to stitch together:

1. The fixed column part: "=$BA$"
2. Your myRow variable
3. The remaining formula logic (with escaped quotes): "=""Y"""

Example Implementation #

Here's a concrete example that ties this to your use case—say you're setting up data validation that updates when your Test variable changes:

Sub UpdateFormulaWithVariableRow()
    Dim Test As Integer
    Dim myRow As Long
    
    ' Replace this with your actual logic to set Test and map it to myRow
    Test = 3 ' Example value for Test
    Select Case Test
        Case 1
            myRow = 13
        Case 2
            myRow = 17
        Case 3
            myRow = 22
        Case Else
            myRow = 10 ' Default row if Test doesn't match a case
    End Select
    
    ' Clean up existing validation first to avoid Run-time error 1004
    On Error Resume Next
    Range("YourTargetCell").Validation.Delete
    On Error GoTo 0
    
    ' Add the updated validation with variable row
    Range("YourTargetCell").Validation.Add _
        Type:=xlValidateCustom, ' Adjust type to match your needs
        AlertStyle:=xlValidAlertStop, _
        Formula1:="=$BA$" & myRow & "=""Y"""
End Sub

How It Works #

• The line Formula1:="=$BA$" & myRow & "=""Y""" builds the final formula string. If myRow is 22, the resulting formula becomes =$BA$22="Y"—exactly what you need, with the column fixed (BA) and row dynamic based on myRow.
• We first delete existing validation to avoid the 1004 error you encountered earlier—this is a common fix for that issue when modifying existing validation rules.

Alternative: Using Format for Readability #

If you prefer cleaner code, you can use the Format function to insert the variable into a template string:

Dim formulaTemplate As String
formulaTemplate = "=$BA${0}=""Y"""
Dim formulaStr As String
formulaStr = Format(formulaTemplate, myRow)

' Then use formulaStr in your Validation.Add call
Range("YourTargetCell").Validation.Add _
    Type:=xlValidateCustom, _
    AlertStyle:=xlValidAlertStop, _
    Formula1:=formulaStr

This makes the formula structure easier to read at a glance, especially if your formula gets more complex.

Key Notes #

• Ensure myRow is a valid Excel row number (between 1 and 1048576 for modern Excel versions).
• Adjust the Type and AlertStyle parameters in Validation.Add to match your specific use case (e.g., xlValidateList if you're using a list instead of custom validation).

内容的提问来源于stack exchange，提问作者Bruno Yuske