help with range in vba function
 

I have that code

public function toto(r as Range) as long
dim i as long

for i = 1 to r.count
toto = toto + r.Cells(1,i).value
if r.Cells(1,i).value < 10 then
r.Cells(1,i).select
with selection.font
.Strikethrough = True
End With
end if
next i

end function

if I'm in the execution window i can type ?toto(activesheet.range("A1:A12"))
that works fine

but if in a cell I type =toto("A1:A12") the fonction does sum the values but
the select and the strikethrough does'nt work.

thanks
Cédric

help with range in vba function
 

I can tell you why it doesn't work, but I'm not sure of
the solution. When a function is calculated in a cell,
that cell remains selected until the function has finished
its calculation. So the Select statements in the function
cannot work.

You could put the code into a sub instead, and get it to
put the sum into the cell below the range.

Hope this helps,
Helen

-----Original Message-----
I have that code

public function toto(r as Range) as long
dim i as long

for i = 1 to r.count
toto = toto + r.Cells(1,i).value
if r.Cells(1,i).value < 10 then
r.Cells(1,i).select
with selection.font
.Strikethrough = True
End With
end if
next i

end function

if I'm in the execution window i can type ?toto
(activesheet.range("A1:A12"))
that works fine

but if in a cell I type =toto("A1:A12") the fonction does
sum the values but
the select and the strikethrough does'nt work.

thanks
Cédric


.

help with range in vba function
 

A function can only return a value to the cell
(or array of cells), from which it was called.
It cannot make any changes to a cell.

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.

"Helen Trim" skrev i en meddelelse
...

I can tell you why it doesn't work, but I'm not sure of
the solution. When a function is calculated in a cell,
that cell remains selected until the function has finished
its calculation. So the Select statements in the function
cannot work.

You could put the code into a sub instead, and get it to
put the sum into the cell below the range.

Hope this helps,
Helen

-----Original Message-----
I have that code

public function toto(r as Range) as long
dim i as long

for i = 1 to r.count
toto = toto + r.Cells(1,i).value
if r.Cells(1,i).value < 10 then
r.Cells(1,i).select
with selection.font
.Strikethrough = True
End With
end if
next i

end function

if I'm in the execution window i can type ?toto
(activesheet.range("A1:A12"))
that works fine

but if in a cell I type =toto("A1:A12") the fonction does
sum the values but
the select and the strikethrough does'nt work.

thanks
Cédric


.