Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Array list writing to an Array of 'scattered cells' ?


How to copy F1:F5 values to a range of scattered cells.

And, can you copy a range of scattered cells to another unlike range of scattered cells?

Howard


Option Explicit
Option Base 0

Sub Copy_Array()

Dim FromArray(5) As Variant
Dim ToArray(5) As Variant
Dim i As Integer

FromArray = Range("F1:F5") ' fails here- 'can't assign to array
ToArray = Range("A1,B2,C3,D4,E5")

For i = LBound(FromArray) To UBound(FromArray)
ToArray(i) = FromArray(i)
Next i

End Sub
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Array list writing to an Array of 'scattered cells' ?

You do not need this line...

Option Base 0

...because arrays are zero-based by default!

One way using arrays:
Sub Copy_Array()
'Do not dimension variants you'll be assigning ranges to!
Dim vaSrc, vaTgt, vTmp, n& '(As Long)

'Size your ranges as contiguous
vaSrc = Range("F1:F5"): vaTgt = Range("A1:E5")

'Replace vaSrc data only in vaTgt
For n = LBound(vaSrc) To UBound(vaSrc)
vaTgt(n, n) = vaSrc(n, 1)
Next 'n

'Assign vaTgt to its range
Range("A1:E5") = vaTgt
End Sub

Another way:
Sub Copy_RngToAreas()
Dim vaSrc, rngTgt As Range, n& '(as long type)

vaSrc = Range("F1:F5")
Set rngTgt = Range("A1,B2,C3,D4,E5")

'Replace vaSrc data only in rngTgt
For n = LBound(vaSrc) To UBound(vaSrc)
rngTgt(n, n) = vaSrc(n, 1)
Next 'n
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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Array list writing to an Array of 'scattered cells' ?

On Monday, March 13, 2017 at 9:24:48 PM UTC-7, GS wrote:
You do not need this line...

Option Base 0

..because arrays are zero-based by default!

One way using arrays:
Sub Copy_Array()
'Do not dimension variants you'll be assigning ranges to!
Dim vaSrc, vaTgt, vTmp, n& '(As Long)

'Size your ranges as contiguous
vaSrc = Range("F1:F5"): vaTgt = Range("A1:E5")

'Replace vaSrc data only in vaTgt
For n = LBound(vaSrc) To UBound(vaSrc)
vaTgt(n, n) = vaSrc(n, 1)
Next 'n

'Assign vaTgt to its range
Range("A1:E5") = vaTgt
End Sub

Another way:
Sub Copy_RngToAreas()
Dim vaSrc, rngTgt As Range, n& '(as long type)

vaSrc = Range("F1:F5")
Set rngTgt = Range("A1,B2,C3,D4,E5")

'Replace vaSrc data only in rngTgt
For n = LBound(vaSrc) To UBound(vaSrc)
rngTgt(n, n) = vaSrc(n, 1)
Next 'n
End Sub

--


Thanks, Garry, those work pretty snappy.

I used the diagonal A1 to E5 to represent what I thought to be 'scattered cells, which are contiguous cells, I think...?

Is there a way to copy F1:F5 cells to cells A1, D5, H9, J6, M11, where these are truly 'scattered'?

And a way to copy say, five truly scattered cells to five other truly scattered cells?

Howard
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Array list writing to an Array of 'scattered cells' ?

Is there a way to copy F1:F5 cells to cells A1, D5, H9, J6, M11, where these
are truly 'scattered'?

And a way to copy say, five truly scattered cells to five other truly
scattered cells?


Try...

Sub Copy_ScatteredCells()
Const sSrc$ = "F1,F2,F3,F4,F5": Const sTgt$ = "A1,D5,H9,J6,M11"
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

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Array list writing to an Array of 'scattered cells' ?

Try...

Sub Copy_ScatteredCells()
Const sSrc$ = "F1,F2,F3,F4,F5": Const sTgt$ = "A1,D5,H9,J6,M11"
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

--
Garry


Excellent, works great!

Thanks Garry.


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Array list writing to an Array of 'scattered cells' ?

Try...

Sub Copy_ScatteredCells()
Const sSrc$ = "F1,F2,F3,F4,F5": Const sTgt$ = "A1,D5,H9,J6,M11"
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

--
Garry


Excellent, works great!

Thanks Garry.


That example works fine with a short list of cell addresses, but longer lists
can be better handled as follows:

Sub Copy_ScatteredCells2()
' This matches src/tgt cell addresses as value pairs
' In cases where copying a ranges of cells to ranges of cells,
' Application.Transpose is used.
Dim v1, v2, n&

'Value-pair the Src|Tgt cell addresses
Const sSrcTgt$ = "F1=A1,F2=D5,F3:F5=H9:J9," _
& "A1:A3=K11:M11,B1:C1=P2:P3"
v1 = Split(sSrcTgt, ",")

On Error GoTo Cleanup
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:
'Error handler code...

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
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Array list writing to an Array of 'scattered cells' ?

That example works fine with a short list of cell addresses, but longer lists
can be better handled as follows:

Sub Copy_ScatteredCells2()
' This matches src/tgt cell addresses as value pairs
' In cases where copying a ranges of cells to ranges of cells,
' Application.Transpose is used.
Dim v1, v2, n&

'Value-pair the Src|Tgt cell addresses
Const sSrcTgt$ = "F1=A1,F2=D5,F3:F5=H9:J9," _
& "A1:A3=K11:M11,B1:C1=P2:P3"
v1 = Split(sSrcTgt, ",")

On Error GoTo Cleanup
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:
'Error handler code...

End Sub

--
Garry


Wow! That's a wagon load! Would seem the "write-to" scenarios are nearly endless.

Thanks for that snippet.

Howard
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Array list writing to an Array of 'scattered cells' ?

That example works fine with a short list of cell addresses, but longer
lists can be better handled as follows:

Sub Copy_ScatteredCells2()
' This matches src/tgt cell addresses as value pairs
' In cases where copying a ranges of cells to ranges of cells,
' Application.Transpose is used.
Dim v1, v2, n&

'Value-pair the Src|Tgt cell addresses
Const sSrcTgt$ = "F1=A1,F2=D5,F3:F5=H9:J9," _
& "A1:A3=K11:M11,B1:C1=P2:P3"
v1 = Split(sSrcTgt, ",")

On Error GoTo Cleanup
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:
'Error handler code...

End Sub

--
Garry


Wow! That's a wagon load! Would seem the "write-to" scenarios are nearly
endless.

Thanks for that snippet.

Howard


You're welcome!
Happy coding...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
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
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Array list writing to an Array of 'scattered cells' ?

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


Regards
Claus B.
--
Windows10
Office 2016


  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
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


  #21   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Array list writing to an Array of 'scattered cells' ?

On Saturday, March 25, 2017 at 8:04:49 AM UTC-7, Claus Busch wrote:
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.


Hi Claus,

Well, that sure works nicely!
This is pretty deep coding for my pay grade, but I can follow most of it reading it, but writing it is a booger.

Thanks, it works very well now.

Howard
  #22   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Array list writing to an Array of 'scattered cells' ?

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


I put Transpose there to accommodate rows-to-cols OR cols-to-rows when
transferring from a datatable to a summary sheet record row; -it makes no
difference otherwise!

My reusable code...

Sub CopyRangeValues(sSrcTgt$, WksSrc As Worksheet, WksTgt As Worksheet)
' Transfers range data between sheets of open workbook[s].
' Uses Transpose in cases where col data copies to summary row record.
'
' Args: sSrcTgt$ String containing range address pairs.
' (SrcRng|TgtRng,SrcRng|TgtRng...)
' WksSrc The sheet the source data gets copied from.
' WksTgt The sheet the source data gets copied to;
' This sheet can be in any open workbook.
'
Dim n&, v1, v2

v1 = Split(sSrcTgt, ",")
For n = LBound(v1) To UBound(v1)
v2 = Split(v1(n), "|")
WksTgt.Range(v2(1)) = Application.Transpose(WksSrc.Range(v2(0)))
Next 'n
End Sub 'CopyRangeValues

...where the sheet refs include their respective parents (workbooks) like so:

Set wksSrc = ThisWorkbook.Sheets("Data")
Set wksTgt = Workbooks("Some.xls").Sheets("Summary")
sSrcTgt = wksSrc.Range("XferAddrs")

CopyRangeValues sSrcTgt, wksSrc, wksTgt

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #23   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Array list writing to an Array of 'scattered cells' ?

FWIW:

Here's another approach I use with a task-specific project that has multiple
workbooks it works with. It can transfer data between various workbooks
according to a table stored in ThisProject.xla:

Sub XferRangeValues()
' Transfers range data between multiple workbooks;
' Range refs are stored in a dynamic named range on "Xfers" sheet;
' Opens/closes workbooks as needed.
'
Dim vXfers, wksSrc As Worksheet, wksTgt As Worksheet
Dim n&, k, v1, v2

vXfers = ThisWorkbook.Sheets("Xfers").Range("XferRefs")
Const sUsrDat$ = ThisWorkbook.Path & "\UserData\"

For n = LBound(vXfers) To UBound(vXfers)
On Error Resume Next
GetSrc:
Set wksSrc = Workbooks(vXfers(n, 1)).Sheets(vXfers(n, 2))
If wksSrc Is Nothing Then '//file not open
Workbooks.Open sUsrDat & vXfers(n, 1): GoTo GetSrc
End If
GetTgt:
Set wksTgt = Workbooks(vXfers(n, 4)).Sheets(vXfers(n, 5))
If wksTgt Is Nothing Then
Workbooks.Open sUsrDat & vXfers(n, 4): GoTo GetTgt
End If
Err.Clear: On Error GoTo Cleanup

v1 = Split(vXfers(n, 3), ","): v2 = Split(vXfers(n, 6), ",')
For k = LBound(v1) To UBound(v1)
wksTgt.Range(v2(k)) = Application.Transpose(wksSrc.Range(v1(k)))
Next 'k
wksSrc.Parent.Close True: wksTgt.Parent.Close True
Next 'n

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

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #24   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Array list writing to an Array of 'scattered cells' ?

I recall being able to keep workbooks open if they were next up so I checked
and found I had revised the 'working' project as follows...


Sub XferRangeValues()
' Transfers range data between multiple workbooks;
' Range refs are stored in a dynamic named range on "Xfers" sheet;
' Opens/closes workbooks as needed.
'
Dim vXfers, wksSrc As Worksheet, wksTgt As Worksheet
Dim n&, k, v1, v2

vXfers = ThisWorkbook.Sheets("Xfers").Range("XferRefs")
Const sUsrDat$ = ThisWorkbook.Path & "\UserData\"

For n = LBound(vXfers) To UBound(vXfers)
On Error Resume Next
GetSrc:
Set wksSrc = Workbooks(vXfers(n, 1)).Sheets(vXfers(n, 2))
If wksSrc Is Nothing Then '//file not open
Workbooks.Open sUsrDat & vXfers(n, 1): GoTo GetSrc
End If
GetTgt:
Set wksTgt = Workbooks(vXfers(n, 4)).Sheets(vXfers(n, 5))
If wksTgt Is Nothing Then
Workbooks.Open sUsrDat & vXfers(n, 4): GoTo GetTgt
End If
Err.Clear: On Error GoTo Cleanup

v1 = Split(vXfers(n, 3), ","): v2 = Split(vXfers(n, 6), ",')
For k = LBound(v1) To UBound(v1)
wksTgt.Range(v2(k)) = Application.Transpose(wksSrc.Range(v1(k)))
Next 'k

If Not vXfers(n + 1, 1) = vXfers(n, 1) Then wksSrc.Parent.Close True
If Not vXfers(n + 1, 4) = vXfers(n, 4) Then wksTgt.Parent.Close True
Next 'n

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


...and updated the component file accordingly. (I store frm/bas/cls files in a
"Src" folder for each project. This is where I pulled code for this thread
from!)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #25   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Array list writing to an Array of 'scattered cells' ?

FWIW:
Just thought I'd share this...


The usage scenario that spawned this process is as follows:

Client-specific tasking to:
a. distribute updated product templates to field reps;
This uses a single source file, multiple target files;
Each field rep's file has an open password, as it also had a 'Notes'
sheet that usually contained confidential rep-specific info;
Each field rep accessed their update file via remote login to the
server.

b. collect previous period files from field reps;
Each field rep uploaded their current working file weekly;
- (this could be during downloading of new templates)
This uses a single target file, multiple source files.

c. distribute various sheets from various files to various staff in
various departments;
This uses various source/target files.

Notes:
The 'Admin' user has restricted access according to their specific need, and so
this was managed in the app via login credentials.

Both field reps and office staff use the same addin. Access to functionality is
controlled via login credentials.

All source/target file require an open password; -this was also stored in the
appropriate Src/Tgt table listing.

All tasks are menu-driven.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
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 05: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 11:24 AM.

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"