Macro problem
 

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

Macro problem
 

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" wrote in
message ...

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

Macro problem
 

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

Macro problem
 

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

Macro problem
 

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" wrote in
message ...

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

Macro problem
 

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

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