Macro problem

[Tomski]

Can anyone tell me the problem with this macro, when run an error is thrown and the returnAddress = MyCell.Address is highlighted.

Function returnAddress(rang As Range, source As String) As Range
For Each MyCell In rang
If MyCell = source Then
returnAddress = MyCell.Address
End If
Next MyCell
End Function


Thanks,

Tom

[Norman Jones]

Hi Tomski,

Try changing:

> Function returnAddress(rang As Range, source As String) As Range

to

Function returnAddress(rang As Range, source As String) As String


---
Regards,
Norman



"Tomski" <[email protected]> wrote in
message news:[email protected]...
>
> Can anyone tell me the problem with this macro, when run an error is
> thrown and the returnAddress = MyCell.Address is highlighted.
>
> Function returnAddress(rang As Range, source As String) As Range
> For Each MyCell In rang
> If MyCell = source Then
> returnAddress = MyCell.Address
> End If
> Next MyCell
> End Function
>
>
> Thanks,
>
> Tom
>
>
> --
> Tomski
> ------------------------------------------------------------------------
> Tomski's Profile:
> http://www.excelforum.com/member.php...o&userid=26824
> View this thread: http://www.excelforum.com/showthread...hreadid=501149
>

[Tomski]

Thats works, but the type is important, as I need to use the returned variable in another function that has a Range for an argument.

If a string is returned then this function doesn't work.

Is it possible to type cast a string to a range?

Cheers,

Tom

[GB]

Instead of just "VARIABLE = New Value" format, you need to use the SET command

SET VARIABLE = NEW VALUE


"Tomski" wrote:

>
> Thats works, but the type is important, as I need to use the returned
> variable in another function that has a Range for an argument.
>
> If a string is returned then this function doesn't work.
>
> Is it possible to type cast a string to a range?
>
> Cheers,
>
> Tom
>
>
> --
> Tomski
> ------------------------------------------------------------------------
> Tomski's Profile: http://www.excelforum.com/member.php...o&userid=26824
> View this thread: http://www.excelforum.com/showthread...hreadid=501149
>
>

[Norman Jones]

Hi Tomski,

Try:

'=============>>
Function ReturnCell(rang As Range, SearchString As String) As Range
Dim MyCell As Range
For Each MyCell In rang
If MyCell.Value = SearchString Then
Set ReturnCell = MyCell
End If
Next MyCell
End Function
'<<=============

I changed ReturnAddress to ReturnCell because the former suggests, to me at
least, a string function. Similarly, I changed Source to SearchString
because the former suggests a range.

---
Regards,
Norman



"Tomski" <[email protected]> wrote in
message news:[email protected]...
>
> Thats works, but the type is important, as I need to use the returned
> variable in another function that has a Range for an argument.
>
> If a string is returned then this function doesn't work.
>
> Is it possible to type cast a string to a range?
>
> Cheers,
>
> Tom
>
>
> --
> Tomski
> ------------------------------------------------------------------------
> Tomski's Profile:
> http://www.excelforum.com/member.php...o&userid=26824
> View this thread: http://www.excelforum.com/showthread...hreadid=501149
>

[Tomski]

Thanks again, I will give it a try on monday and let you know how it goes.

Cheers,

Tom