A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Programming
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Array list writing to an Array of 'scattered cells' ?



 
 
Thread Tools Display Modes
  #11  
Old March 24th 17, 09:59 AM posted to microsoft.public.excel.programming
GS[_6_]
external usenet poster
 
Posts: 695
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
Ads
  #12  
Old March 24th 17, 03:13 PM posted to microsoft.public.excel.programming
L. Howard
external usenet poster
 
Posts: 815
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  
Old March 24th 17, 03:24 PM posted to microsoft.public.excel.programming
L. Howard
external usenet poster
 
Posts: 815
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  
Old March 25th 17, 06:07 AM posted to microsoft.public.excel.programming
L. Howard
external usenet poster
 
Posts: 815
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  
Old March 25th 17, 07:08 AM posted to microsoft.public.excel.programming
GS[_6_]
external usenet poster
 
Posts: 695
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  
Old March 25th 17, 09:16 AM posted to microsoft.public.excel.programming
L. Howard
external usenet poster
 
Posts: 815
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  
Old March 25th 17, 09:52 AM posted to microsoft.public.excel.programming
GS[_6_]
external usenet poster
 
Posts: 695
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  
Old March 25th 17, 09:54 AM posted to microsoft.public.excel.programming
GS[_6_]
external usenet poster
 
Posts: 695
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  
Old March 25th 17, 02:41 PM posted to microsoft.public.excel.programming
L. Howard
external usenet poster
 
Posts: 815
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  
Old March 25th 17, 03:04 PM posted to microsoft.public.excel.programming
Claus Busch
external usenet poster
 
Posts: 3,448
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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 12:40 AM.


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