Excel表格上的SUM，INDIRECT和Char公式 [英] SUM, INDIRECT and Char Formula on a Excel Table

问题描述

我遇到了将SUM公式与间接引用一起用于表引用的情况.

I have got a situation in using the SUM formula with Indirect for Table references.

我想在一个表(名称:Consolidated_062020)中使用该公式，并使用另一个表(名称:IM_062020)的引用并得出SUM.首先，我已记录下来，后来想将其转换为动态公式，因为两个表的名称每个月都会更改一次.

I would like to use the formula in a Table(name: Consolidated_062020) and use the references of the other Table(name: IM_062020) and derive the SUM. Firstly I have recorded that and later would like to convert that into the dynamic formula as both Table names get changed every month.

记录的公式为:

ActiveCell.FormulaR1C1 = "=SUM(INDIRECT(""IM_062020"" & ""["" &[@[Team Members]] & CHAR(10) & Consolidated_062020[[#Headers],[Volumes]] & ""]""))"

我尝试了以下方法将其转换为动态方式:

I tried the following method to convert it into a dynamic way:

Dim TableName As String
Dim TableName2 As String
Dim OLC As ListObject
Dim OLC2 As ListObject

Month = WorksheetFunction.Text(MacroSheetDate, "mm")

Year = WorksheetFunction.Text(MacroSheetDate, "yyyy")

TableName1 = "IM_" & Month & Year
TableName2 = "Consolidated_" & Month & Year

Set OLC = IndMetricsSht.ListObjects(TableName)

Set OLC2 = IndMetricsSht.ListObjects(TableName2)

我在表中选择整个列数据范围，并给出以下公式:

I'm selecting the entire column data range in a table and giving the below-mentioned formula:

Selection.FormulaR1C1 = "=SUM(INDIRECT(" & OLC & "[" & [@[Team Members]] & Chr(10) & OLC2 & [[#Headers],[Volumes]] & "]))"

请帮助！

推荐答案

解决此问题的代码:

Selection.FormulaR1C1 = "=SUM(INDIRECT(" & Chr(34) & TableName & Chr(34) & " & ""["" & [@[Team Members]] & CHAR(10) & ""Volumes"" & ""]""))"

这篇关于Excel表格上的SUM，INDIRECT和Char公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！