Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Modify range of cells at once... | New Users to Excel | |||
Modify search function | Excel Worksheet Functions | |||
Modify range variable passed through Function | Excel Programming | |||
How a function can modify a value from some cells ? | Excel Programming | |||
Can I modify a Hyperlink with a function? | Excel Discussion (Misc queries) |