Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old March 24th 17, 09:59 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 728
Default Array list writing to an Array of 'scattered cells' ?

Another question, if I may?

With the sSrc$ = "A1,C3,E5,G7,I10 range on Sheet3, how would I make the
sTgt$ = "P2,N4,L6,J8,H10" be Sheet4

Howard

Sub Copy_Scattered_Cells_Garry_2()
Const sSrc$ = "A1,C3,E5,G7,I10": Const sTgt$ = "P2,N4,L6,J8,H10"
Dim n&, vaSrc, vaTgt

vaSrc = Split(sSrc, ","): vaTgt = Split(sTgt, ",")

For n = LBound(vaSrc) To UBound(vaSrc)

Range(vaTgt(n)) = Range(vaSrc(n))

Next 'n
End Sub


Typically, source data is on the active sheet and is being sent to a target
sheet which may or may not be in the same workbook. Claus' reply refs both
sheets as being in the same workbook. I'm inclined to ref the workbook so
there's no ambiguity...

Dim wksSrc As Worksheet, wksTgt As Worksheet

Set wksSrc = ThisWorkbook.Sheets("Sheet3")
Set wksTgt = ThisWorkbook.Sheets("Sheet4")

-OR- '//if copying to 1 or more workbooks...

Sub CopyScatteredCells()
Dim wkbSrc As Workbook, wkbTgt As Workbook
Dim wksSrc As Worksheet, wksTgt As Worksheet

Set wkbSrc = ThisWorkbook: Set wkbTgt = Workbooks("Other.xls")
Set wksSrc = wkbSrc.Sheets("Sheet3")
Set wksTgt = wkbTgt.Sheets("Sheet4")

'Do stuff...
wkbTgt.Close SaveChanges:=True

Set wkbTgt = Workbooks.Open("C:\SomeOther.xls")
Set wksTgt = wkbTgt.Sheets("Sheet4")

'Do more stuff...
wkbTgt.Close True

Cleanup: '//error handler exit
Set wksSrc = Nothing: Set wkbSrc = Nothing
Set wksTgt = Nothing: Set wkbTgt = Nothing
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

  #12   Report Post  
Old March 24th 17, 03:13 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2014
Posts: 824
Default Array list writing to an Array of 'scattered cells' ?

On Friday, March 24, 2017 at 2:02:25 AM UTC-7, Claus Busch wrote:
Hi Howard,

Am Fri, 24 Mar 2017 01:56:27 -0700 (PDT) schrieb L. Howard:

For n = LBound(vaSrc) To UBound(vaSrc)

Range(vaTgt(n)) = Range(vaSrc(n))

Next 'n


try:

For n = LBound(vaSrc) To UBound(vaSrc)
Sheets("Sheet4") .Range(vaTgt(n)) =Sheets("Sheet3"). Range(vaSrc(n))
Next 'n


Hi Claus,

Works perfect... I can't believe I had it correct EXCEPT for
Sheets(Sheet4).Range(vaTgt(n))... where I was not using " "'s.

DUH

Thanks, Howard
  #13   Report Post  
Old March 24th 17, 03:24 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2014
Posts: 824
Default Array list writing to an Array of 'scattered cells' ?

On Friday, March 24, 2017 at 2:59:42 AM UTC-7, GS wrote:
Another question, if I may?

With the sSrc$ = "A1,C3,E5,G7,I10 range on Sheet3, how would I make the
sTgt$ = "P2,N4,L6,J8,H10" be Sheet4

Howard

Sub Copy_Scattered_Cells_Garry_2()
Const sSrc$ = "A1,C3,E5,G7,I10": Const sTgt$ = "P2,N4,L6,J8,H10"
Dim n&, vaSrc, vaTgt

vaSrc = Split(sSrc, ","): vaTgt = Split(sTgt, ",")

For n = LBound(vaSrc) To UBound(vaSrc)

Range(vaTgt(n)) = Range(vaSrc(n))

Next 'n
End Sub


Typically, source data is on the active sheet and is being sent to a target
sheet which may or may not be in the same workbook. Claus' reply refs both
sheets as being in the same workbook. I'm inclined to ref the workbook so
there's no ambiguity...

Dim wksSrc As Worksheet, wksTgt As Worksheet

Set wksSrc = ThisWorkbook.Sheets("Sheet3")
Set wksTgt = ThisWorkbook.Sheets("Sheet4")

-OR- '//if copying to 1 or more workbooks...

Sub CopyScatteredCells()
Dim wkbSrc As Workbook, wkbTgt As Workbook
Dim wksSrc As Worksheet, wksTgt As Worksheet

Set wkbSrc = ThisWorkbook: Set wkbTgt = Workbooks("Other.xls")
Set wksSrc = wkbSrc.Sheets("Sheet3")
Set wksTgt = wkbTgt.Sheets("Sheet4")

'Do stuff...
wkbTgt.Close SaveChanges:=True

Set wkbTgt = Workbooks.Open("C:\SomeOther.xls")
Set wksTgt = wkbTgt.Sheets("Sheet4")

'Do more stuff...
wkbTgt.Close True

Cleanup: '//error handler exit
Set wksSrc = Nothing: Set wkbSrc = Nothing
Set wksTgt = Nothing: Set wkbTgt = Nothing
End Sub

--
Garry


Hi Garry,

I built the two Other/SomeOther sheets and it works perfect.

Thanks a lot for the code. It sure is fast, but there's not very much data.

Howard
  #14   Report Post  
Old March 25th 17, 06:07 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2014
Posts: 824
Default Array list writing to an Array of 'scattered cells' ?

I'm hung up on another code conversion for scattered cells to other workbooks.
The code below works just fine copying to workbooks named "Other" and "SomeOther" to any sheet I want.

I am trying to do this Const to workbook "Other":
Const sSrc$ = "A1,C3,E5,G7,I10": Const sTgt$ = "P2,N4,L6,J8,H10"

And I want this Const to go to workbook "SomeOther":
'Value-pair the Src|Tgt cell addresses
Const sSrcTgt$ = "A4:A6=O4:O6,C5:C8=P5:P8,A9=Q9,B11=R11"

Where you would use:

For n = LBound(v1) To UBound(v1)
'Parse the Src=Tgt cell addresses
v2 = Split(v1(n), "=")
Sheets("Sheet4").Range(v2(1)) = Application.Transpose(Range(v2(0)))
Next 'n

Not able to get it to work, I have copied the Dim's as needed for it. Not getting any errors, just no output to workbook "SomeOther".
(the output to workbook "Other" works as it should even though the ranges for "SomeOther" don't)

Howard


(this is unmodified and works fine, has none of my attempted conversions in it)
Sub CopyScatteredCells_SomeOther_Workbooks_XXX()
Const sSrc$ = "A1,C3,E5,G7,I10": Const sTgt$ = "P2,N4,L6,J8,H10"

Dim n&, vaSrc, vaTgt
Dim wkbSrc As Workbook, wkbTgt As Workbook
Dim wksSrc As Worksheet, wksTgt As Worksheet

' Set wkbSrc = ThisWorkbook: Set wkbTgt = Workbooks("SomeOther.xlsm") 'can do it this way OR two lines
Set wkbSrc = ThisWorkbook
Set wkbTgt = Workbooks("Other.xlsm")
Set wksSrc = wkbSrc.Sheets("Sheet3")
Set wksTgt = wkbTgt.Sheets("Sheet2") '/ sheet2 or whatever on "Other"

vaSrc = Split(sSrc, ","): vaTgt = Split(sTgt, ",")

For n = LBound(vaSrc) To UBound(vaSrc)
wksTgt.Range(vaTgt(n)) = wksSrc.Range(vaSrc(n))
Next 'n
' wkbTgt.Close SaveChanges:=True

Set wkbTgt = Workbooks("SomeOther.xlsm")
Set wksTgt = wkbTgt.Sheets("Sheet4") '/ sheet4 or whatever on "SomeOther"

vaSrc = Split(sSrc, ","): vaTgt = Split(sTgt, ",")

For n = LBound(vaSrc) To UBound(vaSrc)
wksTgt.Range(vaTgt(n)) = wksSrc.Range(vaSrc(n))
Next 'n
' wkbTgt.Close True

Cleanup: '//error handler exit
Set wksSrc = Nothing: Set wkbSrc = Nothing
Set wksTgt = Nothing: Set wkbTgt = Nothing
End Sub
  #15   Report Post  
Old March 25th 17, 07:08 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 728
Default Array list writing to an Array of 'scattered cells' ?

I'm hung up on another code conversion for scattered cells to other
workbooks. The code below works just fine copying to workbooks named "Other"
and "SomeOther" to any sheet I want.

I am trying to do this Const to workbook "Other":
Const sSrc$ = "A1,C3,E5,G7,I10": Const sTgt$ = "P2,N4,L6,J8,H10"

And I want this Const to go to workbook "SomeOther":
'Value-pair the Src|Tgt cell addresses
Const sSrcTgt$ = "A4:A6=O4:O6,C5:C8=P5:P8,A9=Q9,B11=R11"

Where you would use:

For n = LBound(v1) To UBound(v1)
'Parse the Src=Tgt cell addresses
v2 = Split(v1(n), "=")
Sheets("Sheet4").Range(v2(1)) = Application.Transpose(Range(v2(0)))
Next 'n

Not able to get it to work, I have copied the Dim's as needed for it. Not
getting any errors, just no output to workbook "SomeOther". (the output to
workbook "Other" works as it should even though the ranges for "SomeOther"
don't)

Howard


There's no reason for it to work with any workbook other than ActiveWorkbook
because there's no explicit ref to any other workbook! The ref to
ActiveWorkbook is implicit.


(this is unmodified and works fine, has none of my attempted conversions in
it) Sub CopyScatteredCells_SomeOther_Workbooks_XXX()
Const sSrc$ = "A1,C3,E5,G7,I10": Const sTgt$ = "P2,N4,L6,J8,H10"

Dim n&, vaSrc, vaTgt
Dim wkbSrc As Workbook, wkbTgt As Workbook
Dim wksSrc As Worksheet, wksTgt As Worksheet

' Set wkbSrc = ThisWorkbook: Set wkbTgt = Workbooks("SomeOther.xlsm") 'can
do it this way OR two lines Set wkbSrc = ThisWorkbook
Set wkbTgt = Workbooks("Other.xlsm")
Set wksSrc = wkbSrc.Sheets("Sheet3")
Set wksTgt = wkbTgt.Sheets("Sheet2") '/ sheet2 or whatever on "Other"

vaSrc = Split(sSrc, ","): vaTgt = Split(sTgt, ",")

For n = LBound(vaSrc) To UBound(vaSrc)
wksTgt.Range(vaTgt(n)) = wksSrc.Range(vaSrc(n))
Next 'n
' wkbTgt.Close SaveChanges:=True

Set wkbTgt = Workbooks("SomeOther.xlsm")
Set wksTgt = wkbTgt.Sheets("Sheet4") '/ sheet4 or whatever on
"SomeOther"

vaSrc = Split(sSrc, ","): vaTgt = Split(sTgt, ",")


These arrays are already loaded with these strings and so do not need to be
reloaded unless the refs change.

For n = LBound(vaSrc) To UBound(vaSrc)
wksTgt.Range(vaTgt(n)) = wksSrc.Range(vaSrc(n))
Next 'n
' wkbTgt.Close True

Cleanup: '//error handler exit
Set wksSrc = Nothing: Set wkbSrc = Nothing
Set wksTgt = Nothing: Set wkbTgt = Nothing
End Sub


Just curious why the target workbooks are macro enabled...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


  #16   Report Post  
Old March 25th 17, 09:16 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2014
Posts: 824
Default Array list writing to an Array of 'scattered cells' ?

I don't understand this...

There's no reason for it to work with any workbook other than ActiveWorkbook
because there's no explicit ref to any other workbook! The ref to
ActiveWorkbook is implicit.



Is it possible to make this go to workbook "Other"...
Const sSrc$ = "A1,C3,E5,G7,I10": Const sTgt$ = "P2,N4,L6,J8,H10"

And this go to workbook "SomeOther"...?
Const sSrcTgt$ = "A4:A6=O4:O6,C5:C8=P5:P8,A9=Q9,B11=R11"

I always save my workbooks as macro enabled, but I don't have an exact reason, other than I get a alert box scolding me if I don't.

Howard


  #17   Report Post  
Old March 25th 17, 09:52 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 728
Default Array list writing to an Array of 'scattered cells' ?

Here's how I'd do it...


Sub Copy_SrcToTgt()
Dim n&, v1, v2
Dim wksSrc As Worksheet, wksTgt As Worksheet

Const sSrc$ = "A1,C3,E5,G7,I10": Const sTgt$ = "P2,N4,L6,J8,H10"
Const sSrcTgt$ | "A4:A6|O4:O6,C5:C8|P5:P8,A9|Q9,B11|R11"

'Set ref to source sheet
Set wksSrc = ThisWorkbook.Sheets("Sheet3")
On Error GoTo Cleanup

'Set 1st target sheet and process it
Set wksTgt = Workbooks("Other.xls").Sheets("Sheet2")
v1 = Split(sSrc, ","): v2 = Split(sTgt, ",")
For n = LBound(vaSrc) To UBound(vaSrc)
wksTgt.Range(v2(n)) = wksSrc.Range(v1(n))
Next 'n

'Set 2nd target sheet and process it
Set wksTgt = Workbooks("SomeOther.xls").Sheets("Sheet4")
v1 = Split(sSrcTgt, ",")
For n = LBound(v1) To UBound(v1)
'Parse the Src:Tgt cell addresses
v2 = Split(v1(n), "|")
Range(v2(1)) = Application.Transpose(Range(v2(0)))
Next 'n

Cleanup:
Set wksSrc = Nothing: Set wksTgt = Nothing
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #18   Report Post  
Old March 25th 17, 09:54 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 728
Default Array list writing to an Array of 'scattered cells' ?

Typo...


Sub Copy_SrcToTgt()
Dim n&, v1, v2
Dim wksSrc As Worksheet, wksTgt As Worksheet

Const sSrc$ = "A1,C3,E5,G7,I10": Const sTgt$ = "P2,N4,L6,J8,H10"

Const sSrcTgt$ = "A4:A6|O4:O6,C5:C8|P5:P8,A9|Q9,B11|R11"

'Set ref to source sheet
Set wksSrc = ThisWorkbook.Sheets("Sheet3")
On Error GoTo Cleanup

'Set 1st target sheet and process it
Set wksTgt = Workbooks("Other.xls").Sheets("Sheet2")
v1 = Split(sSrc, ","): v2 = Split(sTgt, ",")
For n = LBound(vaSrc) To UBound(vaSrc)
wksTgt.Range(v2(n)) = wksSrc.Range(v1(n))
Next 'n

'Set 2nd target sheet and process it
Set wksTgt = Workbooks("SomeOther.xls").Sheets("Sheet4")
v1 = Split(sSrcTgt, ",")
For n = LBound(v1) To UBound(v1)
'Parse the Src:Tgt cell addresses
v2 = Split(v1(n), "|")
Range(v2(1)) = Application.Transpose(Range(v2(0)))
Next 'n

Cleanup:
Set wksSrc = Nothing: Set wksTgt = Nothing
End Sub


--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #19   Report Post  
Old March 25th 17, 02:41 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2014
Posts: 824
Default Array list writing to an Array of 'scattered cells' ?

I got an error at first and added Dim vaSrc.
Now I get no error but no copy to the workbooks.

I changed the extension in the code from .xls to .xlsm for the target workbooks.

Here is the code as I modified it, any suggestions?

Thanks, Howard

Sub Copy_Range_to_Range_Single_to_Single()
Dim n&, v1, v2

Dim wksSrc As Worksheet, wksTgt As Worksheet
Dim vaSrc


Const sSrc$ = "A1,C3,E5,G7,I10": Const sTgt$ = "P2,N4,L6,J8,H10"
Const sSrcTgt$ = "A4:A6|O4:O6,C5:C8|P5:P8,A9|Q9,B11|R11"

'Set ref to source sheet
Set wksSrc = ThisWorkbook.Sheets("Sheet3")

On Error GoTo Cleanup

'Set 1st target sheet and process it
Set wksTgt = Workbooks("Other.xlsm").Sheets("Sheet2")
v1 = Split(sSrc, ","): v2 = Split(sTgt, ",")

For n = LBound(vaSrc) To UBound(vaSrc)

wksTgt.Range(v2(n)) = wksSrc.Range(v1(n))
Next 'n

'Set 2nd target sheet and process it
Set wksTgt = Workbooks("SomeOther.xlsm").Sheets("Sheet4")
v1 = Split(sSrcTgt, ",")

For n = LBound(v1) To UBound(v1)

'Parse the Src:Tgt cell addresses
v2 = Split(v1(n), "|")

Range(v2(1)) = Application.Transpose(Range(v2(0)))
Next 'n

Cleanup:

Set wksSrc = Nothing: Set wksTgt = Nothing
End Sub


  #20   Report Post  
Old March 25th 17, 03:04 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,466
Default Array list writing to an Array of 'scattered cells' ?

Hi Howard,

Am Sat, 25 Mar 2017 07:41:34 -0700 (PDT) schrieb L. Howard:

I got an error at first and added Dim vaSrc.
Now I get no error but no copy to the workbooks.


your didn't refer correctly. And the ranges have the same direction so
you don't have to transpose.

Try:

Sub Copy_Range_to_Range_Single_to_Single()
Dim n&, v1, v2

Dim wksSrc As Worksheet, wksTgt As Worksheet
Dim vaSrc


Const sSrc = "A1,C3,E5,G7,I10"
Const sTgt = "P2,N4,L6,J8,H10"
Const sSrcTgt = "A4:A6|O4:O6,C5:C8|P5:P8,A9|Q9,B11|R11"

'Set ref to source sheet
Set wksSrc = ThisWorkbook.Sheets("Sheet3")

On Error GoTo Cleanup

'Set 1st target sheet and process it
Set wksTgt = Workbooks("Other.xlsm").Sheets("Sheet2")
v1 = Split(sSrc, ","): v2 = Split(sTgt, ",")

For n = LBound(v1) To UBound(v1)

wksTgt.Range(v2(n)) = wksSrc.Range(v1(n))
Next 'n

'Set 2nd target sheet and process it
Set wksTgt = Workbooks("SomeOther.xlsm").Sheets("Sheet4")
v1 = Split(sSrcTgt, ",")

For n = LBound(v1) To UBound(v1)

'Parse the Src:Tgt cell addresses
v2 = Split(v1(n), "|")

wksTgt.Range(v2(1)).Value = wksSrc.Range(v2(0)).Value
Next 'n

Cleanup:

Set wksSrc = Nothing: Set wksTgt = Nothing
End Sub


Regards
Claus B.
--
Windows10
Office 2016


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
Scattered array cells copy to scattered array cells another workbook L. Howard Excel Programming 14 July 14th 14 04:13 PM
Reading variable list of cells into array Diffus Excel Programming 5 October 1st 08 07:03 PM
Redimming an array dynamically assigned from range (how to redim first dimension of a 2-D array? /or/ reverse the original array order) Keith R[_2_] Excel Programming 3 November 13th 07 04:08 PM
combining cells and array from different sheets into an array to pass to IRR() [email protected] Excel Discussion (Misc queries) 3 September 11th 06 07:17 AM
Writing Range to Array Marston Excel Programming 3 August 9th 04 09:11 PM


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

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017