首页 文章

Excel VBA按其他工作表上的单元格值进行筛选并使用公式

提问于
浏览 次
-1

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$1:$T$15001").AutoFilter Field:=1, Criteria1:=zipcode
    ActiveSheet.Range("$A$1:$T$15001").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 = answered 我有一张包含多张纸的工作簿，但我主要关注的两张纸是Sheet（"Enter Info"）和Sheet（"Sold Homes"）

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

1st 我需要通过邮政编码过滤15,000行 . 邮政编码保存在单元格B2中的"enter info"表上 . 邮政编码位于"Sold Homes"表的Field = 1中 .

2nd 我需要根据房屋的卧室数量来优化现在过滤的数据 . 住宅的卧室数量保存在单元格K2的“输入信息”表中 . 卧室的数量在"Sold Homes"表上的字段= 10中找到 .

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

Question 2

在“售完房屋”表单上过滤数据后，我需要找到现在显示的房屋的平均销售价格 . 每个住宅的销售价格的数据可在“售出的房屋”表的“R”栏中找到 . 最大的复杂因素是，在应用两个过滤条件后返回的房屋数量各不相同 .

例如：邮编11111，卧室数= 3，返回50个家 . 因此，我想平均50个家庭

但

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

我需要一种方法来仅选择R列中具有美元金额的单元格（非空白）作为我的平均函数 . 如果我选择整个列R，我会得到15,000个主页列表中每个房子的平均值，因为过滤会像隐藏不在所选参数中的单元格那样 .

非常感谢提前 . 对不起，我第一次没有发布我的代码 .

excel vba excel-vba excel-formula

1 回答

  • 0

    这应该做：

    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
    回复于

相关问题