title: Excel VBA filtering by cell value on other sheets and using formula
body: <p><strong>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</strong> </p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>Question 1 = answered</strong>
  I have a workbook with many sheets but the two sheets I am primarily concerned with are Sheet("Enter Info") and Sheet("Sold Homes")</p>
  
  <p>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:</p>
  
  <p><strong>1st</strong> I 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.</p>
  
  <p><strong>2nd</strong> I 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.</p>
  
  <p>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.</p>
  
  <p><strong>Question 2</strong></p>
  
  <p>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. </p>
  
  <p>Ex: zipcode 11111, Bedroom count = 3, returns 50 homes. Therefore, I would like the average of those 50 homes</p>
  
  <p>BUT</p>
  
  <p>Zipvode 22222, bedroom count = 4, returns 36 homes. </p>
  
  <p>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. </p>
  
  <p>Thanks so much in advance. Sorry I did not post my code the 1st time.</p>