Using Range object as created
 

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.

Using Range object as created No.2.
 

....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...

Using Range object as created
 

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


--
Regards,
Tom Ogilvy

"zsola" wrote in message
...
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.

Using Range object as created
 

....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.



-----Original Message-----
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


--
Regards,
Tom Ogilvy

"zsola" wrote in message
...
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.


.

Using Range object as created
 

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.

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

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.

--
Regards,
Tom Ogilvy


"zsola" wrote in message
...
...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.



-----Original Message-----
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


--
Regards,
Tom Ogilvy

"zsola" wrote in message
...
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.


.

Using Range object as created
 

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

Szabo Zsolt, Hungary


-----Original Message-----
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.

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

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.

--
Regards,
Tom Ogilvy


"zsola" wrote in message
...
...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.



-----Original Message-----
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


--
Regards,
Tom Ogilvy

"zsola" wrote in message
...
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.


.



.