Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 14th 17, 01:28 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2014
Posts: 840
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  
Old March 14th 17, 05:24 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 763
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  
Old March 14th 17, 07:16 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2014
Posts: 840
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  
Old March 14th 17, 10:38 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 763
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  
Old March 14th 17, 04:58 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2014
Posts: 840
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  
Old March 14th 17, 07:25 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 763
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  
Old March 15th 17, 03:03 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2014
Posts: 840
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  
Old March 15th 17, 03:09 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 763
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  
Old March 24th 17, 09:56 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2014
Posts: 840
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  
Old March 24th 17, 10:02 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,535
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


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 03:03 AM.

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