title: Ouput function result of calculation to multiple Cells in Excel using VBA
body: <p>Im using VBA to program a function in excel that will search a list looking for certain names, count when certain sought for names come up and then output these counter values to individual cells.</p>
  
  <p>How do I allocate the values to the function itself when I have a multi cell function? Ive chosen 4 cells next to each other in the same column and pressed CTRL-SHFT-ENTER to get a multi cell function I just dont know how to allocate results to the function so that it will show in the selected cells. What I've done so far is shown below:</p>
  
  <pre><code>Function ROM(ByVal lookup_value As Range, _
  ByVal lookup_column As Range, _
  ByVal return_value_column As Long) As String 
  
  Application.ScreenUpdating = False
  
  Dim i As Long
  Dim resultCount As Long
  Dim resultsArray() As String
  Dim arraySize As Long
  Dim myrange As Range
  Dim results As String
  Dim TSS As Long
  Dim OSS As Long
  Dim AWS As Long
  Dim JLI As Long
  Dim answers(1 To 3, 1 To 1) As Variant
  
  
  ' The following code works out how many matches there are for the lookup and creates an
  ' array of the same size to hold these results
  
  Set myrange = lookup_column
  arraySize = Application.WorksheetFunction.CountIf(myrange, lookup_value.Value)
  ReDim resultsArray(arraySize - 1)
  
  ' A counter for the results
  
  resultCount = 0
  TSS = 0
  OSS = 0
  AWS = 0
  JLI = 0
  
  ' The equipment ID column is looped through and for every match the corresponding Equipment Type is
  ' saved into the resultsArray for analysis
  
  For i = 1 To lookup_column.Rows.count
      If Len(lookup_column(i, 1).Text) &lt;&gt; 0 Then
          If lookup_column(i, 1).Text = lookup_value.Value Then
  
                  ' If statement to ensure that the function doesnt cycle to a number larger than the
                  ' size of resultsArray
  
                  If (resultCount &lt; (arraySize)) Then
                      resultsArray(resultCount) = (lookup_column(i).Offset(0, return_value_column).Text)
                      results = (lookup_column(i).Offset(0, return_value_column).Text)
                      resultCount = resultCount + 1
                          ' The following code compares the string to preset values and increments
                          ' the counters if any are found in the string
  
                          If (InStr(results, "TPWS TSS") &gt; 0) Then
                              TSS = TSS + 1
  
                          ElseIf (InStr(results, "TPWS OSS")) Then
                              OSS = OSS + 1
  
                          ElseIf (InStr(results, "JUNCTION INDICATOR (1 Route)") &gt; 0) Then
                              JLI = JLI + 1
  
                          ElseIf (InStr(results, "AWS")) Then
                              AWS = AWS + 1
  
                          End If
  
                   End If
          End If
      End If
  Next
  
   answers(1, 1) = TSS
   answers(1, 2) = OSS
   answers(1, 3) = AWS
   answers(1, 4) = 0
  
    ROM = answers    
  
  
  Application.ScreenUpdating = True
  
  
  End Function
  </code></pre>
  
  <p>When I try running the function it keeps saying type mismatch for answers. The cells selected for the multi cell formula are F18, G18, H18 and I18.</p>