Using Range object as created

[zsola]

Hi again,

I tried writing a function using a "rng" and "cella" Range
variable in it. My plan is to walking through cells in
this range, or only in one single column of rng and
calculate with cell's value. The code sample should be
something like this:

Function proba(ByVal cella, rng As Range) As Integer
Dim item, rng1 As Range

Set rng1 = rng.Columns(1)

For Each item In rng1
proba = item.Value + cella.Value
Next item
End Function


And this code doesn't work. I think my solution in
reference to range "rng1" or cell is wrong, but I don't
have idea.

 

[zsola]

....so I think, my main problem is how to declare, create
of an range variable and referring its elements as cells
and use its values in procedures or functions...

 

[Tom Ogilvy]

Function proba(cella As Range, rng As Range) As Long
Dim itm as Range, rng1 As Range

Set rng1 = rng.Columns(1).Cells

For Each itm In rng1
if isnumeric(cella.Value) then
proba = itm.Value + cella.Value
End if
Next item
End Function

 

[zsola]

....and it really works now. Thanks again.
Would you be so kind as to give me some explanation or a
reference/link where I can get it.

 

[Tom Ogilvy]

I really isn't much different from what you had

You must declare each variable individually

Dim i, j as Integer declares j as an integer and i as variant.

I would pass in Cella by ref since that is the default and you are not
trying to change it anyway. (it was my assumption that Cella is a single
cell reference. You could also pass it in as a double.

Set rng1 = rng.Columns(1).Cells

I put cells on the end because you could get a rng1 reference that is a
single column object. Then you can't really loop through that. Adding cells
insures it is individual cells in a column.

here we loop through the cells in rng1 using the itm to hold a reference to
each cell sequentially.

then we check that cella is number, but that should have been a check if itm
isnumeric. Also, I am wasn't exactly sure what you were trying to do, so I
didn't disturb your code unless i though it was problematic. If you are
trying to add up all the cells in rng1 with the value of cella added to
each, then it should be


For Each itm In rng1
if isnumeric(itm.Value) then
proba = proba + itm.Value + cella.Value
End if
Next item

If you can't figure out the functionality, post back with a description of
what you want your function to do.

 

[zsola]

Thanks for explanation!
I have learnt much from you and understood.

Szabo Zsolt, Hungary