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
  #1  
Old March 14th 17, 12:28 AM posted to microsoft.public.excel.programming
L. Howard
external usenet poster
 
Posts: 815
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
Ads
  #2  
Old March 14th 17, 04:24 AM posted to microsoft.public.excel.programming
GS[_6_]
external usenet poster
 
Posts: 713
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  
Old March 14th 17, 06: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' ?

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  
Old March 14th 17, 09:38 AM posted to microsoft.public.excel.programming
GS[_6_]
external usenet poster
 
Posts: 713
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  
Old March 14th 17, 03:58 PM posted to microsoft.public.excel.programming
L. Howard
external usenet poster
 
Posts: 815
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  
Old March 14th 17, 06:25 PM posted to microsoft.public.excel.programming
GS[_6_]
external usenet poster
 
Posts: 713
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  
Old March 15th 17, 02:03 AM posted to microsoft.public.excel.programming
L. Howard
external usenet poster
 
Posts: 815
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  
Old March 15th 17, 02:09 AM posted to microsoft.public.excel.programming
GS[_6_]
external usenet poster
 
Posts: 713
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  
Old March 24th 17, 08:56 AM posted to microsoft.public.excel.programming
L. Howard
external usenet poster
 
Posts: 815
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  
Old March 24th 17, 09:02 AM posted to microsoft.public.excel.programming
Claus Busch
external usenet poster
 
Posts: 3,451
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
 




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 02:09 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.