VBA function modify a range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Suresh - 23 Mar 2006 16:27 GMT

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.

Reply to this Message

Crowbar - 23 Mar 2006 17:37 GMT

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

Reply to this Message

Suresh - 23 Mar 2006 18:13 GMT

> Your question makes no sense to me so this is a guess
>
[quoted text clipped - 3 lines]
> 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.

Reply to this Message