You can do the same thing with the Array function, but there is one possible
problem point (actually, this problem point occurs when using the
intermediate variable method as well)... Split always returns a zero-based
array no matter what the Option Base setting is (it is an unusual function
in that respect) whereas the Array function respects the Option Base
setting, so you have to be careful that you reference the correct element
number when using this method with the Array function.

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
Thanks' Rick,

I didn't know that.

Mike

"Rick Rothstein" wrote:

If you want to save a variable, you can do your code in one line...

vTargetWS.Range("A1") = Split(vSourceWS.Range("A1"), "/")(1)

Split creates an array, so instead of assigning that array to a variable
just to get to the 2nd element (Split always returns a zero-based array),
you can grab that 2nd element directly from the array being created by
the
Split function itself.

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
Hi,

If your example formula waorks in ALL cases then this should too

Dim Parts As Variant
Parts = Split(vSourceWS.Range("A1"), "/")
vTargetWS.Range("A1") = Parts(1)


Mike

"Atif" wrote:

Hi all,
i am using following code to copy values from Source Workbook to
Target
Workbook, its working fine.
vSourceWS.Range("A1").Copy Destination:=vTargetWS.Range("A1")

information at source is in following format:
/SCC/4

TargetWorkbook only requires "SCC", following funtion fulfill this
requirement
MID(A1,(FIND("/",A1)+1),FIND("/",A1,2)-2)

How can i achive this goal using VBA, without entering formula in
Worksheet.

Regards

Atif

.