如何在VBA生成的Excel复杂公式中添加$符号实现绝对引用？

Hey there! I see you're trying to convert the relative reference Cars!C4 to an absolute reference Cars!$C$4 in your VBA-generated formula, and you've run into a "type mismatch" error when trying manual $ concatenation. Let's solve this properly using the built-in Range.Address method parameters instead—this is the cleanest and most reliable way.

The Root Issue #

Your original code uses Address(False, False) for the Cars sheet cell reference, which forces both row and column to be relative. To get an absolute reference, we just need to adjust the parameters of the Address method instead of manually adding $ symbols (which can lead to syntax/type errors if not done perfectly).

Modified VBA Code #

Here's the corrected version of your code, with the key change highlighted:

With Worksheets(LabourSheet)
    .Cells(PosStartLineCalc + 1, PosStartColumnLt).Formula = _
        "=IF(or(" & .Cells(PosStartLineCalc, PosStartColumnLt).Address(False, False) & "=""-""," & _
        .Cells(PosStartLineCalc, PosStartColumnLt).Address(False, False) & "=""X""),""-""," & _
        "Cars!" & .Cells(PosStartLine + (TPICode * 3) - 3, Split(Cells(1, col).Address, "$")(1)).Address(True, True) & "*" & _
        .Cells(PosStartLineCalc, PosStartColumnLt).Address(False, False) & "*Data!$C$8)"
End With

What Changed? #

I replaced Address(False, False) with Address(True, True) for the Cars sheet cell reference. Here's what those parameters mean:

• The first True sets row absolute (locks the row number with $)
• The second True sets column absolute (locks the column letter with $)

Resulting Formula #

After this change, the generated formula will look exactly like what you need:

=IF(OR(C4="-";C4="X");"-";Cars!$C$4C4Data!$C$8)

Why Manual $ Concatenation Failed #

When you tried adding $ directly (or using Chr(36)), it's easy to mess up the string concatenation syntax—especially if the variables involved aren't properly cast to strings. Using the Address method's built-in parameters avoids these pitfalls entirely because it handles the reference formatting for you.

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