title: Function to merge excel data referencing a count using VBA
body: <p>I'm trying to write a function that merges multiple rows of text in a column into a single cell based on a pre determined count. My goal is to generate a flexible function to aid in compiling / interperting large quantaties of data. The code I've written returns #NAME? and I cant figure out where the error is. My code is as follows:</p>
  
  <pre><code>Function vmrg(countref As Integer, datref As Integer) As String
  If IsEmpty(ActiveCell.Offset(0, -countref)) Then % check if cell containing count is blank
  vertmerge = "N/A"                                % if blank, state N/A
  Else
      Dim datlst(0 To ActiveCell.Offset(0, -countref).Value - 1) As String 
      Dim i As Integer                                                      
      For i = 0 To ActiveCell.Offset(0, -countref).Value - 1  
          datlst(i) = ActiveCell.Offset(i, -datref).Text  %fill array with data
      End
      vertmerge = datlst(0)
      For i = 1 To ActiveCell.Offset(0, -countref).Value - 1  % merge array to a single string
          vertmerge = vertmerge &amp; ", " &amp; datlst(i)
          End
     End
  End Function
  </code></pre>
  
  <p>I have matlab and some C++ experience but this is the first time I've used VBA so my syntax is probably odd in some areas and wrong in others.  Ideally I would like to reference the cells where the data and count info are stored, but for now I'm hoping to correct my syntax and set a jumping off point for further development of this function. Any reccomendations are appreciated.</p>
  
  <p>Code Rev_1: I still have an output of #NAME? but I think I've corrected(?) some of the issues</p>
  
  <pre><code>Function vertmerge(countref As Range, datref As Integer) As String
  If IsEmpty(countref) = True Then
  vertmerge = "NA"
  Else
  Dim datlst(0 To countref.Value - 1) As String
  Dim i As Integer
  For i = 0 To countref.Value - 1
  datlst(i) = countref.Offset(i, datref).Text
  Next i
  vertmerge = datlst(0)
  For i = 1 To countref.Value - 1
  vertmerge = vertmerge &amp; ", " &amp; datlst(i)
  Next i
  End
  End Function
  </code></pre>