Call Statement and Return Value

[kathy.aubin]

Hi,

I just created a function in a personal macro. I'm trying to use the
Call statement to call my function within my Sub but by doing this, I
loose the return value of the function. I'm looking for a way to use
the return value of the function. Is there an other way to call my
function? A way I can preserve the return value?

Here is my code :

Function SelectRangeToChange(ToChange As Range, X As Range, Value As
String)
Dim Selection As Range, Cell As Range
Set Selection = Intersect(X, ActiveSheet.UsedRange)
For Each Cell In Selection
If (Cell.Value) = "Value" Then
If ToChange Is Nothing Then
Set ToChange = Cell
Else: Set ToChange = Union(ToChange, Cell)
End If
End If
Next Cell
End Function

Sub SarkFundFormat()

Cells.Select
Selection.Sort Key1:=Range("C2"), Header:=xlYes
Dim ToDelete As Range, CashSelection As Range, Cash As String
Call SelectRangeToChange(ToDelete, Range("C:C"), Cash)

-> here is my problem. I'm trying to refer to the ToDelete range but I
can't

ToDelete.EntireRow.Copy
Sheets("Cash & FX").Paste
ToDelete.EntireRow.Delete

End Sub

Thanks in advance!

 

[Guest]

A function has to return a value but you never set the retrun value

Function SelectRangeToChange(ToChange As Range, X As Range, Value As
String) As Range '???
Dim Selection As Range, Cell As Range 'don't use selection reserved word
Set Selection = Intersect(X, ActiveSheet.UsedRange)
For Each Cell In Selection
If (Cell.Value) = "Value" Then
If ToChange Is Nothing Then
Set ToChange = Cell
Else: Set ToChange = Union(ToChange, Cell)
End If
End If
Next Cell
set SelectRangeToChange = ToChange '???
End Function

sub Test
dim rng as range

set rng = SelectRangeToChange(...)

end sub

Note: in a function if you do not specify the return type then the function
returns a variant which is not too efficient...

 

[kathy.aubin]

Thanks,

Thanks seems to set rng to the return value of my function but when I
try to use the rng range to do something like :

rng.select

It's not working. So I don't know if it's really sets as a range.

 

[Guest]

selects are a bit of a beast to deal with. I avoid them like the plague. You
can not select a range that is not on the active sheet... for example

dim rngThis as Range
dim rngThat as Range

set rngThis = sheets("This").Range("A1")
set rngThat = sheets("That").Range("A1")

Sheets("This").select
rngThis.select 'This is good
rngThat.select 'This will generate a run time error...