Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,058
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,501
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,501
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
calling a worksheet function from another worksheet in same workbo Liz Excel Programming 5 June 23rd 08 06:16 PM
Using dcount function within user-defined worksheet function pongthai Excel Programming 3 January 15th 07 10:55 AM
Reference the worksheet from a multiple worksheet range function ( DBickel Excel Worksheet Functions 1 May 28th 05 03:49 AM
Can the offset worksheet function reference another worksheet AlistairJ Excel Worksheet Functions 2 May 9th 05 06:18 PM
formula/function to copy from worksheet to worksheet Jen Excel Programming 5 January 11th 05 09:22 PM


All times are GMT +1. The time now is 08:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"