title: Excel vba Function:filtering
body: <p>I am trying to write an excel function that will output a range containing a subset of values based on a value range and a filter range and a criteria.
  Using the example below, I want to pass Range("A2:A5") and  Range("B2:B5") to output Range("C2:C5")  </p>
  
  <p>+---+-------+-------+-------+</p>
  
  <p>|   |   A   |   B   |   C   |</p>
  
  <p>+---+-------+-------+-------+</p>
  
  <p>| 1 | Name  | Group | Output|</p>
  
  <p>| 2 | Nick  | A     |  Nick |</p>
  
  <p>| 3 | Marc  | A     | Marc  |</p>
  
  <p>| 4 | Manny | B     | Luck  |</p>
  
  <p>| 5 | Luck  | A     |       |</p>
  
  <p>+---+-------+-------+-------+</p>
  
  <p>I have written the code below so far. it works fine in vba but not when I call the function within the sheet
  D1.function -> =filterFN(A2:A6,B2:B6,D1,"B")</p>
  
  <p>:</p>
  
  <pre><code>Sub test()
      Sheets("Sheet2").Range("D1") = filterFN(Sheets("Sheet2").Range("A1:A6"), Sheets("Sheet2").Range("B1:B6"), Sheets("Sheet2").Range("D1"), "B")
  End Sub
  
  Function filterFN(valueRange As Range, filterRange As Range, outputRange As Range, criteriaStr As String)
  
      Dim Arr1() As Variant ' declare an unallocated array.
      Dim Arr2() As Variant
      Dim Arr3(50) As Variant
  
      Dim i, j As Integer
  
      Arr1 = valueRange
      Arr2 = filterRange
  
      For i = 1 To UBound(Arr1, 1)
          If Arr2(i, 1) = criteriaStr Then
              Arr3(j) = Arr1(i, 1)
              j = j + 1
          End If
  
      Next i
  
      [outputRange].Resize(UBound(Arr3)) = Application.Transpose(Arr3)
      filterFN = Arr3(0)
  
  End Function
  </code></pre>
  
  <p>Many thanks in advance</p>