Accessing single array value returned from a function

[patbt]

Hi,Having a brain freeze this morning. I have two sheets with various bits of information in them and am looking to construct a third sheet which combines some of this information. I am writing a function which returns an array and I want to use one fo the values in the arrayI started with a simple return function to make sure I was finding the right values in Sheet 1

Function VLUM(rng As Range, myVal As Variant, colref)
   Dim i                           As Long

   For i = 1 To rng.Rows.Count
      If rng.Cells(i, 1).value = myVal Then VLUM = VLUM & "," & rng.Cells(i, colref).value
   Next
   VLUM = Mid$(VLUM, 2)
End Function

This works. Great, so I am getting the right information. I then thought I just need to reference the value in the array for the correct position, not the entire array. However putting a choose or index statement in front of my vlum function call in the cell doesn't work (#value returned), adding it as a parameter passed in doesn't work either. I'm at a little bit of a loss. All the examples I can find are for using a bunch of cells as an array, I just want to use the array returned by my function as the array to an argument). Its early and I am missing something obvious. Please excuse my stupidity. I literally want to get an array value form the array returned by my function. I even tried hard-codign it in to return an array value, but I still get a Value# error

Function aVLUM(rng As Range, myVal As Variant, colref)
   Dim i                           As Long
   For i = 1 To rng.Rows.Count
      If rng.Cells(i, 1).value = myVal Then aVLUMa = aVLUMa & "," & rng.Cells(i, colref).value
   Next   'cut off the first , value
   aVLUMa = Mid$(aVLUMa, 2)
   aVLUM = aVLUMa(3)
End Function

 

[RoryA]

Try this version to return an array:

Function aVLUM(rng As Range, myVal As Variant, colref)
   Dim i                           As Long
   For i = 1 To rng.Rows.Count
      If rng.Cells(i, 1).value = myVal Then aVLUMa = aVLUMa & "," & rng.Cells(i, colref).value
   Next   'cut off the first , value
   aVLUM = Split(Mid$(aVLUMa, 2), ",")
End Function

 

[patbt]

Thanks Rory, works great

 

[RoryA]

Glad to help. Welcome to the forum, by the way.