Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > microsoft.public.excel.programming > #109911
| From | GS <gs@v.invalid> |
|---|---|
| Newsgroups | microsoft.public.excel.programming |
| Subject | Re: Array list writing to an Array of 'scattered cells' ? |
| Date | 2017-03-14 14:25 -0400 |
| Organization | A noiseless patient Spider |
| Message-ID | <oa9cdj$uf7$1@dont-email.me> (permalink) |
| References | <8b82fe28-bd0a-45db-80f0-6df276c73c1d@googlegroups.com> <oa7r4v$ra$1@dont-email.me> <07dcf9cd-fbfc-4f16-8f82-9ad1ddab6ff5@googlegroups.com> <oa8dgu$ftq$1@dont-email.me> <5ee81bb0-1d99-4802-9e9e-8ff059616fd3@googlegroups.com> |
>> 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
Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread
Array list writing to an Array of 'scattered cells' ? "L. Howard" <lhkittle@comcast.net> - 2017-03-13 17:28 -0700
Re: Array list writing to an Array of 'scattered cells' ? GS <gs@v.invalid> - 2017-03-14 00:24 -0400
Re: Array list writing to an Array of 'scattered cells' ? "L. Howard" <lhkittle@comcast.net> - 2017-03-13 23:16 -0700
Re: Array list writing to an Array of 'scattered cells' ? GS <gs@v.invalid> - 2017-03-14 05:38 -0400
Re: Array list writing to an Array of 'scattered cells' ? "L. Howard" <lhkittle@comcast.net> - 2017-03-14 08:58 -0700
Re: Array list writing to an Array of 'scattered cells' ? GS <gs@v.invalid> - 2017-03-14 14:25 -0400
Re: Array list writing to an Array of 'scattered cells' ? "L. Howard" <lhkittle@comcast.net> - 2017-03-14 19:03 -0700
Re: Array list writing to an Array of 'scattered cells' ? GS <gs@v.invalid> - 2017-03-14 22:09 -0400
Re: Array list writing to an Array of 'scattered cells' ? "L. Howard" <lhkittle@comcast.net> - 2017-03-24 01:56 -0700
Re: Array list writing to an Array of 'scattered cells' ? Claus Busch <claus_busch@t-online.de> - 2017-03-24 10:02 +0100
Re: Array list writing to an Array of 'scattered cells' ? "L. Howard" <lhkittle@comcast.net> - 2017-03-24 08:13 -0700
Re: Array list writing to an Array of 'scattered cells' ? GS <gs@v.invalid> - 2017-03-24 05:59 -0400
Re: Array list writing to an Array of 'scattered cells' ? "L. Howard" <lhkittle@comcast.net> - 2017-03-24 08:24 -0700
Re: Array list writing to an Array of 'scattered cells' ? "L. Howard" <lhkittle@comcast.net> - 2017-03-24 23:07 -0700
Re: Array list writing to an Array of 'scattered cells' ? GS <gs@v.invalid> - 2017-03-25 03:08 -0400
Re: Array list writing to an Array of 'scattered cells' ? "L. Howard" <lhkittle@comcast.net> - 2017-03-25 02:16 -0700
Re: Array list writing to an Array of 'scattered cells' ? GS <gs@v.invalid> - 2017-03-25 05:52 -0400
Re: Array list writing to an Array of 'scattered cells' ? GS <gs@v.invalid> - 2017-03-25 05:54 -0400
Re: Array list writing to an Array of 'scattered cells' ? "L. Howard" <lhkittle@comcast.net> - 2017-03-25 07:41 -0700
Re: Array list writing to an Array of 'scattered cells' ? Claus Busch <claus_busch@t-online.de> - 2017-03-25 16:04 +0100
Re: Array list writing to an Array of 'scattered cells' ? "L. Howard" <lhkittle@comcast.net> - 2017-03-25 11:05 -0700
Re: Array list writing to an Array of 'scattered cells' ? GS <gs@v.invalid> - 2017-03-25 18:28 -0400
Re: Array list writing to an Array of 'scattered cells' ? GS <gs@v.invalid> - 2017-03-25 21:29 -0400
Re: Array list writing to an Array of 'scattered cells' ? GS <gs@v.invalid> - 2017-03-25 22:41 -0400
Re: Array list writing to an Array of 'scattered cells' ? GS <gs@v.invalid> - 2017-03-28 12:49 -0400
csiph-web