Passing a range to a function

[David Bell]

Formul in Excel looks like this:
=TestForEach(F7:J8)

Function TestForEach(a As Range)
Dim cell As Range
For Each cell In Range(a)
Msgbox("Hello")
Next
End Function

Whats worg with this picture. I am trying to pass a range of cells from
excel to this function and nothing happens. For some reason I am not using
the "a" range correctly.

[Nick Hodge]

David

Not sure what your function does but a is already set to a range so just use
a

Function TestForEach(a As Range)
Dim cell As Range
For Each cell In a
Msgbox("Hello")
Next
End Function


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]HIS


"David Bell" <David [email protected]> wrote in message
news:[email protected]...
> Formul in Excel looks like this:
> =TestForEach(F7:J8)
>
> Function TestForEach(a As Range)
> Dim cell As Range
> For Each cell In Range(a)
> Msgbox("Hello")
> Next
> End Function
>
> Whats worg with this picture. I am trying to pass a range of cells from
> excel to this function and nothing happens. For some reason I am not using
> the "a" range correctly.

[Nick Hodge]

slight correct...know what it's doing...don't know why ;-)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]HIS


"Nick Hodge" <[email protected]> wrote in message
news:[email protected]...
> David
>
> Not sure what your function does but a is already set to a range so just
> use a
>
> Function TestForEach(a As Range)
> Dim cell As Range
> For Each cell In a
> Msgbox("Hello")
> Next
> End Function
>
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> www.nickhodge.co.uk
> [email protected]HIS
>
>
> "David Bell" <David [email protected]> wrote in message
> news:[email protected]...
>> Formul in Excel looks like this:
>> =TestForEach(F7:J8)
>>
>> Function TestForEach(a As Range)
>> Dim cell As Range
>> For Each cell In Range(a)
>> Msgbox("Hello")
>> Next
>> End Function
>>
>> Whats worg with this picture. I am trying to pass a range of cells from
>> excel to this function and nothing happens. For some reason I am not
>> using
>> the "a" range correctly.
>
>

[David Bell]

I need to check a range of cells for materials choosin for a beam,
ie. DF, DF #2 & Btr, DF #1, PSL, LVL, ect

They may select 1 or several materials to use for the beam check.
The user needs to send me a range where the materials are specified and then
I will do calculations to determine the min size and material for the beam
and / or give them a choice of all amterials and sizes allowable

Formula in Excel looks like this:
=TestForEach(F7:J8)

Function TestForEach(a As Range)
Dim cell As Range
For Each cell In Range(a)
'Msgbox("Hello")
Call CalculatMinimumBeamSize(cell, returnvalue)
Next
End Function

[David Bell]

I got it working, Thanks for the help. The problem was the "in a" instead of
"Range(a)" and using "cell" for the data