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

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

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

Show all headers | View raw

> 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

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