Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calling a worksheet function from another worksheet in same workbo | Excel Programming | |||
Using dcount function within user-defined worksheet function | Excel Programming | |||
Reference the worksheet from a multiple worksheet range function ( | Excel Worksheet Functions | |||
Can the offset worksheet function reference another worksheet | Excel Worksheet Functions | |||
formula/function to copy from worksheet to worksheet | Excel Programming |