List of items in an array - VBA?
 

Hello

Excel 2003 query around VBA and how you access a Range

Consider two cols of data with people's favourite colour

A B
1) Fred Red
2) Sid Red
3) Bob Blue
4) Andy Reg
5) Steve Blue

What I want is to write the following
=funcWhoCol(A1:B5,"Blue")

The function will return a string containing the names of people whose
favourite colour matches the second parameter, in this case the strung
returned would be "Bob Steve"

I started to create a function and pass the range in as the first param
and the match string as the second but I can't find how to manipulate
the range in my function.

What I want is logically as shown below (although I have no idea about
how to do it so the code is nonsense)

function funcWhoCol is (inRange, inMatch)

for i_count = 1 to inRange.Cells.Count
if inRange(i_count) = inMatch then funcWhoCol = funcWhoCol +
inRange(i_count) + " "
next i_count

end function


Many thanks in advance
Craig

List of items in an array - VBA?
 

Maybe something like:

Option Explicit
Function FuncWhoCol(inRange As Range, inMatch As String) As String

Dim i_Count As Long
Dim myStr As String
Dim myCell As Range

'add as many validity checks as you can think of
If inRange.Areas.Count 1 Then
FuncWhoCol = "Too many Areas!"
Exit Function
End If

If inRange.Columns.Count < 2 Then
FuncWhoCol = "Not Two Columns!"
Exit Function
End If

'do the real work

myStr = ""
For Each myCell In inRange.Columns(2).Cells
If LCase(myCell.Value) = LCase(inMatch) Then
myStr = myStr & " " & myCell.Offset(0, -1).Value
End If
Next myCell

If myStr < "" Then
myStr = Mid(myStr, 2)
End If

FuncWhoCol = myStr

End Function

CNEWS wrote:

Hello

Excel 2003 query around VBA and how you access a Range

Consider two cols of data with people's favourite colour

A B
1) Fred Red
2) Sid Red
3) Bob Blue
4) Andy Reg
5) Steve Blue

What I want is to write the following
=funcWhoCol(A1:B5,"Blue")

The function will return a string containing the names of people whose
favourite colour matches the second parameter, in this case the strung
returned would be "Bob Steve"

I started to create a function and pass the range in as the first param
and the match string as the second but I can't find how to manipulate
the range in my function.

What I want is logically as shown below (although I have no idea about
how to do it so the code is nonsense)

function funcWhoCol is (inRange, inMatch)

for i_count = 1 to inRange.Cells.Count
if inRange(i_count) = inMatch then funcWhoCol = funcWhoCol +
inRange(i_count) + " "
next i_count

end function

Many thanks in advance
Craig

--

Dave Peterson

List of items in an array - VBA?
 

Dave

You are a genius

That is just the job

Many, many thanks

C