Excel VBA 按其他工作表上的单元格值过滤并使用公式

Excel VBA filtering by cell value on other sheets and using formula

提问by Snook55

So I figured out the first part of my question- how to filter by values on other worksheets, the code I used is shown bellow. However, I cannot figure out what I am doing wrong with "average" formula portion

所以我想出了我问题的第一部分 - 如何按其他工作表上的值过滤，我使用的代码如下所示。但是，我无法弄清楚“平均”公式部分做错了什么

Sub Filtering_Average()
'
' Filtering Macro


'Turn off filtering that has already been applied
Worksheets("Sold Homes").Cells.AutoFilter


Worksheets("Sold Homes").Range("A1").Select

Dim zipcode As Range, bedroom As Range, soldprice As Range
  Set zipcode = Worksheets("Enter Info").Range("B2")
  Set bedroom = Worksheets("Enter Info").Range("K2")
'defining "soldprice" as range that starts in R2 and continues until a blank cell, on sheet "Sold Homes"
    Set soldprice = Worksheets("Sold Homes").Range("R2",     Range("R1").End(xlDown))

'Filtering data in Sold Homes sheet based on zip code and Bedroom count
    ActiveSheet.Range("$A:$T001").AutoFilter Field:=1, Criteria1:=zipcode
    ActiveSheet.Range("$A:$T001").AutoFilter Field:=10, Criteria1:=bedroom

'go to the sheet "Enter Info"
Worksheets("Enter Info").Select
'Select cell "AM16"
Range("am16").Select
'Calculate the average of the cells defined by "soldprice"
ActiveCell.Formula = "=Average(soldprice)"


'
End Sub

Question 1 = answeredI have a workbook with many sheets but the two sheets I am primarily concerned with are Sheet("Enter Info") and Sheet("Sold Homes")

问题 1 = 已回答我有一个包含许多工作表的工作簿，但我主要关注的两张工作表是 Sheet("Enter Info") 和 Sheet("Sold Homes")

As you may have guessed the "sold homes" sheet holds data on homes that have sold in my area. I have data on 15,000 homes, each in its own row. I need to filter that data by 2 conditions:

正如您可能已经猜到的那样，“已售房屋”表包含我所在地区已售房屋的数据。我有 15,000 个家庭的数据，每个家庭都有自己的行。我需要按 2 个条件过滤该数据：

1stI need to filter the 15,000 rows by the zip code. The zip code is held on "enter info" sheet in cell B2. The zip code is found in Field = 1 on the "Sold Homes" sheet.

首先，我需要按邮政编码过滤 15,000 行。邮政编码保存在单元格 B2 的“输入信息”表中。邮政编码位于“已售房屋”表上的 Field = 1 中。

2ndI need to refine the now filtered data by the number of bedrooms the homes have. The # of bedrooms that a home has is held on the Enter Info sheet in cell K2. The number of bedrooms is found in field = 10 on the "Sold Homes" Sheet.

第二，我需要通过家庭拥有的卧室数量来优化现在过滤的数据。家庭拥有的卧室数量保存在单元格 K2 的输入信息表中。卧室数量在“已售房屋”表上的字段 = 10 中找到。

I know how to write the VBA macro to do a simple filter by zip code and then filter by a specified number of bedrooms but I need the macro to adapt and filter based on what I place into cell B2 and K2 on my "enter info" sheet.

我知道如何编写 VBA 宏来按邮政编码进行简单的过滤，然后按指定数量的卧室进行过滤，但我需要该宏根据我在“输入信息”中放入单元格 B2 和 K2 的内容进行调整和过滤床单。

Question 2

问题2

After the data is filtered on the "sold homes" sheet I need to find the average sale price for the homes that are now showing. The data for the Sale price of each home is found in column "R" of the "sold homes" sheet. The biggest complication of this is that the number of homes returned after the two filtering conditions have been applied varies.

在“已售房屋”表上过滤数据后，我需要找到现在显示的房屋的平均销售价格。每个房屋的销售价格数据可在“已售房屋”表的“R”列中找到。最大的复杂之处在于，应用了两种过滤条件后返回的房屋数量会有所不同。

Ex: zipcode 11111, Bedroom count = 3, returns 50 homes. Therefore, I would like the average of those 50 homes

例如：邮政编码 11111，卧室数 = 3，返回 50 个房屋。因此，我想要这 50 个家庭的平均值

BUT

但

Zipvode 22222, bedroom count = 4, returns 36 homes.

Zipvode 22222，卧室数 = 4，返回 36 个家庭。

I would need a way to select only the cells that are in column R that have a Dollar amount in them (Not Blank) for my average function. If I select the entire column R I get the average for every house in the 15,000 home list because filtering does something similar to hiding cells that are not in the selected parameters.

我需要一种方法来仅选择 R 列中具有美元金额（非空白）的单元格用于我的平均函数。如果我选择整列 RI，将获得 15,000 个家庭列表中每个房屋的平均值，因为过滤的作用类似于隐藏不在所选参数中的单元格。

Thanks so much in advance. Sorry I did not post my code the 1st time.

非常感谢。抱歉，我第一次没有发布我的代码。

回答by user3598756

this should do:

这应该做：

Option Explicit

Sub Filtering_Average()
    '
    ' Filtering Macro
    '
    Dim zipcode As Range, bedroom As Range, soldprice As Range

    With Worksheets("Enter Info")
        Set zipcode = .Range("B2")
        Set bedroom = .Range("K2")
        Set soldprice = .Range("AM16")
    End With

    With Worksheets("Sold Homes")
        With .Range("A1:T" & .Cells(.Rows.count, "R").End(xlUp).Row)
            .AutoFilter 'Turn off any previous filtering
            .AutoFilter Field:=1, Criteria1:=zipcode
            .AutoFilter Field:=10, Criteria1:=bedroom
            If Application.WorksheetFunction.Subtotal(103, .Resize(, 1)) > 1 Then soldprice.Value = WorksheetFunction.Average(.Resize(.Rows.count - 1, 1).Offset(1, 17).SpecialCells(xlCellTypeVisible))
        End With
        .AutoFilterMode = False
    End With
End Sub