VBA function modify a range
 

Hi,

I am sorry if this is a too basic question. Unfortunately I couldnot find a
solution so far.

I wish to write a function in VBA which would modify the cells (which is
passed as a parameter) and also return a value. Something like this:

Public Function macro1(add As Range)as String
add.Select
Selection.Value = 10
macro1="hello"
End Function

But this does not work. "add.Select" does not seem to have any effect.

But if I write it as a Sub, it works

Public Sub macro1(add As Range)
add.Select
Selection.Value = 10
End Sub

But I actually need this to be a function.

Any help or pointer would be greatly appreciated.


Thanks.

VBA function modify a range
 

Your question makes no sense to me so this is a guess


Sub auto_open
For x = 1 to 1000
If sheet1.cells(x, 1).value = 10 then
msgbox "Line " & x & " = 10"
end sub

--
Message posted via http://www.officekb.com

VBA function modify a range
 

"Crowbar via OfficeKB.com" <u15117@uwe wrote in message
news:5db125701bdcb@uwe...
Your question makes no sense to me so this is a guess


Sub auto_open
For x = 1 to 1000
If sheet1.cells(x, 1).value = 10 then
msgbox "Line " & x & " = 10"
end sub


Thanks for your reply. But thats not what I want.

I wish to write a function in VBA, which would take a range as the
parameter. This function would then fill the range with some values. And
finally it should return the total number of values added.

I could then place the function in (say) cell A1

A1: =macro(B1:B10)

This should then fill the range B1:B10 with (say) numbers from 1 to 10, and
should return 10 (which would appear in A1).

I have seen a macro-function that does this sort of thing, but unfortunately
I dont have access to the code.

I tried to make Range("B1:B10").select within the macro, but this does not
have any effect. Selection.address would return "$A$1".



Thanks again.