SUBTOTAL超过254行 [英] SUBTOTAL more than 254 rows

问题描述

我通过预算数据通过VBA自动生成电子表格。行是可变的。我使用以下代码进行R1C1公式。变量r是行计数器。

 '----------------- 
'小计公式
'----------------- 
'构建公式字符串
 formulaString == SUBTOTAL（109
 For i = 13 To r  -  13 
 formulaString = formulaString&，R [& i&] C
i = i + 11 
 Next i 
 formulaString = formulaString&）
 
'小计公式
 .Range（I5：U6）。FormulaR1C1 = formulaString 
  pre> 
 
 
这个工作很好，直到引用超过254.有一个简单的方法来构建这个R1C1公式，使得：对于范围内的每个单元格I5：U6，SUBTOTAL 109每13下排到最大行？如果有一个简单的答案，你可以一步一步解释一下吗？谢谢。
解决方案
如果您无法对数据进行排序以避免这种情况，或者在用户定义的函数中运行总和，那么可以添加第二个循环计数到254，当你打254，关闭公式字符串并重新打开一个，如更新的代码：
 '构建公式字符串
 formulaString == SUBTOTAL（109
 lCount = 1 
对于i = 13 To r  -  13 
 formulaString = formulaString&，R [& i&] C
i = i + 11 
 lcount = lcount + 1 
 if lcount> 250 then 
 formulaString = formulaString&）+ SUBTOTAL （109
 lcount = 1 
 end if 
 Next i 
 formulaString = formulaString&）
 
'小计公式
。范围（I5：U6）。FormulaR1C1 = formulaString 
  
 
I am auto generating a spreadsheet via VBA with budgetary data. The rows are variable. I use the following code for an R1C1 formula. Variable r is a row counter.
        '-----------------
        'Subtotal formula
        '-----------------
        'Build formula string
        formulaString = "=SUBTOTAL(109"
        For i = 13 To r - 13
            formulaString = formulaString & ",R[" & i & "]C"
            i = i + 11
        Next i
        formulaString = formulaString & ")"

        'Subtotal formulas
        .Range("I5:U6").FormulaR1C1 = formulaString
This works great until the references exceed 254. Is there an easy way build this R1C1 formula such that: For each cell in range I5:U6, SUBTOTAL 109 every 13th row beneath to max row? If there is an easy answer could you please explain it step by step? Thanks.
解决方案
If you can't sort your data to avoid that, or run the sum in a user defined function, you can add a second loop to count until 254 and when you hit 254, close the formula string and reopen one, as in this updated code:
    'Build formula string
    formulaString = "=SUBTOTAL(109"
    lCount=1
    For i = 13 To r - 13
        formulaString = formulaString & ",R[" & i & "]C"
        i = i + 11
        lcount=lcount+1
        if lcount>250 then 
           formulaString = formulaString & ")+ SUBTOTAL(109"
           lcount=1
        end if                        
    Next i
    formulaString = formulaString & ")"

    'Subtotal formulas
    .Range("I5:U6").FormulaR1C1 = formulaString


                        
这篇关于SUBTOTAL超过254行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！