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...
--
HTH...

Jim Thomlinson


" wrote:

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!