Question

我有一个宏观方案,将公式化成两维范围,并正确地增加相对地址。然而,范围是“硬编码”,我需要能够以方案方式确定范围深度。我的法典使用方括号来确定范围,似乎你可以在方括号内使用一个变量(或者至少我可以说明如何这样做)。

我需要该守则使用一个变量来界定范围的最底层右侧角。我的想法非常有益。

我想取代<代码>[T3:DX321]。公式...

注


    Dim Lastrow As Long
    Lastrow = BillSch.Range("S" & Rows.Count).End(xlUp).row   Last row of BillSch
        
         This code works but I need the lastrow of DX to be a variable
        [T3:DX321].Formula = "=IFERROR(XLOOKUP(1,(BS_ORDERNO=$S3)*(BS_WEEKNO=T$1),BS_CHARGEDPRICE),"""")"

        These two attempts were failures
         [T3:DX" & lastrow].Formula = "=IFERROR...."   ERROR--  "Runtime error 434 Object required

         Dim Rng As Range
         Rng = BillSch.Range("T3:DX" & Lastrow)   ERROR--  "Object variable or With block variable not set"
         Rng.Formula = "=IFERROR(XLOOKUP(1,(BS_ORDERNO=$S3)*(BS_WEEKNO=T$1),BS_CHARGEDPRICE),"""")"
End Sub

Answer 1

[..] in your code is equalent to Evaluate(".."). It s better to use Range(..) object.

    Range("T3:DX" & Lastrow).Formula = "=IFERROR(XLOOKUP(1,(BS_ORDERNO=$S3)*(BS_WEEKNO=T$1),BS_CHARGEDPRICE),"""")"

友情链接