创建if则将范围作为值的公式 [英] Create if then formula with ranges as values

问题描述

我有一个数据透视表，我想使用if then公式进行动态分析.我根据自己的条件创建了两个范围.该范围是我所需的数据透视字段中的选定单元格.

I have a pivot table I wanted to dynamically analyze with an if then formula. I created two ranges based on my criteria. The ranges are selected cells in my desired pivot field.

例如，对于Range1，我找到它的枢轴字段，向下移动一个，然后将其选择为范围.

For instance, for Range1 I have it find the pivot field, move down one, and then select that as the range.

我的公式如下所示，并且可以运行，但是公式中显示的是

My formula looks like this and works, but what shows up in the formula is

 =if(myrange > .6%, myrange2, "")

而不是实际值.

我担心的另一个问题是，当我尝试自动填充更多值时，这两个范围保持静态并且不会向下移动.

The other problem I fear is that the two ranges with stay static and not move down when I try and auto fill more values.

这是我的代码

 Sub blah()
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables(1)

    'range1

    pt.ColumnRange.Find("jack").Select
    Selection.Offset(1, 0).Select
    Dim myrange As range
    Set myrange = Selection
    MsgBox myrange

    'range2

    pt.RowRange.Find("steve").Select
    Selection.Offset(1, 0).Select
    Dim myrange2 As range
    Set myrange2 = Selection
    MsgBox myrange & myrange2

    'where i want the formula to go

    pt.ColumnRange.End(xlToRight).Select
    Selection.Offset(1, 0).Select
    Selection.End(xlToLeft).Select
    Selection.Offset(1, 0).Select
    Selection.Offset(0, 1).Select

    'formula

    ActiveCell.FormulaR1C1 = "myrange>0.3%,myrange2,"""")"

    'this part doesnt work but i'd like it to autofill to the end of the pivot

    Selection.AutoFill Destination:=range("P7:P36588")
    End Sub

推荐答案

我不明白一件事:

如果您已经知道该公式将在"P7:P36588"范围内，那么为什么需要所有这些代码来查找要在其中编写公式的单元格?

if you already know that the formula will be in range "P7:P36588" why do you need all this code to find the cell where you want to write the formula?

Sub blah()
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)

'range1
Dim myrange As range
Set myrange = pt.ColumnRange.Find("jack").Offset(1, 0)
MsgBox myrange

'range2
Dim myrange2 As range
Set myrange2 =  pt.RowRange.Find("steve").Offset(1, 0)
MsgBox myrange & myrange2

'formula
 frml = "=if(" & myrange.Address(0,0) & ">0.3%," & myrange2.Address(0,0) & ","""")"

'where i want the formula to go
pt.ColumnRange.End(xlToRight).Offset(1, 0).End(xlToLeft).Offset(1,1).Formula = frml

ActiveSheet.range("P7:P36588").Filldown
End Sub

这篇关于创建if则将范围作为值的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！