Accessing single array value returned from a function

patbt

New Member
Joined
Sep 9, 2013
Messages
2
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
Code:
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
Code:
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
 
Last edited by a moderator:

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,706
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Try this version to return an array:
Code:
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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,706
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Glad to help. Welcome to the forum, by the way. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,763
Messages
5,638,215
Members
417,014
Latest member
dualwieldbacon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top