Worksheet function
 

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

Worksheet function
 

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

--
Gary''s Student - gsnu200909


"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

Worksheet function
 

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

Worksheet function
 

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

Worksheet function
 

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.

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message
...
Sub qwerty()
s = "/SCC/4"
t = Split(s, "/")
MsgBox t(1)
End Sub

--
Gary''s Student - gsnu200909


"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

Worksheet function
 

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

.

Worksheet function
 

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

.