Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #109940

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

Show all headers | View raw

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

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

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