Worksheet function

[Atif]

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

 

[Gary''s Student]

Sub qwerty()
s = "/SCC/4"
t = Split(s, "/")
MsgBox t(1)
End Sub

 

[Mike H]

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

 

[Rick Rothstein]

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 Rothstein]

You can do your macro in one line of code...

Sub qwerty()
MsgBox Split("/SCC/4", "/")(1)
End Sub

which means the variable or range reference containing the "/SCC/4" string
can be used in place of the hard-coded text you showed in your example.

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.

 

[Mike H]

Thanks' Rick,

I didn't know that.

Mike

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)




.

 

[Rick Rothstein]

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.