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

Groups > microsoft.public.excel.programming > #109905 > unrolled thread

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

Started by"L. Howard" <lhkittle@comcast.net>
First post2017-03-13 17:28 -0700
Last post2017-03-28 12:49 -0400
Articles 5 on this page of 25 — 3 participants

Back to article view | Back to microsoft.public.excel.programming

Contents

  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

Page 2 of 2 — ← Prev page 1 [2]

#109941

From"L. Howard" <lhkittle@comcast.net>
Date2017-03-25 11:05 -0700
Message-ID<e3c2b4a0-5bbb-4305-8688-f112c3374507@googlegroups.com>
In reply to#109940
On Saturday, March 25, 2017 at 8:04:49 AM UTC-7, Claus Busch wrote:
> 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.

Hi Claus,

Well, that sure works nicely!  
This is pretty deep coding for my pay grade, but I can follow most of it reading it, but writing it is a booger.

Thanks, it works very well now.

Howard

[toc] | [prev] | [next] | [standalone]

#109942

FromGS <gs@v.invalid>
Date2017-03-25 18:28 -0400
Message-ID<ob6qp5$quq$1@dont-email.me>
In reply to#109940
> your didn't refer correctly. And the ranges have the same direction so
> you don't have to transpose.

I put Transpose there to accommodate rows-to-cols OR cols-to-rows when 
transferring from a datatable to a summary sheet record row; -it makes no 
difference otherwise!

My reusable code...

Sub CopyRangeValues(sSrcTgt$, WksSrc As Worksheet, WksTgt As Worksheet)
' Transfers range data between sheets of open workbook[s].
' Uses Transpose in cases where col data copies to summary row record.
'
' Args:   sSrcTgt$  String containing range address pairs.
'                     (SrcRng|TgtRng,SrcRng|TgtRng...)
'         WksSrc    The sheet the source data gets copied from.
'         WksTgt    The sheet the source data gets copied to;
'                     This sheet can be in any open workbook.
'
  Dim n&, v1, v2

  v1 = Split(sSrcTgt, ",")
  For n = LBound(v1) To UBound(v1)
     v2 = Split(v1(n), "|")
     WksTgt.Range(v2(1)) = Application.Transpose(WksSrc.Range(v2(0)))
   Next 'n
End Sub 'CopyRangeValues

..where the sheet refs include their respective parents (workbooks) like so:

  Set wksSrc = ThisWorkbook.Sheets("Data")
  Set wksTgt = Workbooks("Some.xls").Sheets("Summary")
  sSrcTgt = wksSrc.Range("XferAddrs")

  CopyRangeValues sSrcTgt, wksSrc, wksTgt

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#109943

FromGS <gs@v.invalid>
Date2017-03-25 21:29 -0400
Message-ID<ob75c8$i59$1@dont-email.me>
In reply to#109905
FWIW:

Here's another approach I use with a task-specific project that has multiple 
workbooks it works with. It can transfer data between various workbooks 
according to a table stored in ThisProject.xla:

Sub XferRangeValues()
' Transfers range data between multiple workbooks;
' Range refs are stored in a dynamic named range on "Xfers" sheet;
' Opens/closes workbooks as needed.
'
  Dim vXfers, wksSrc As Worksheet, wksTgt As Worksheet
  Dim n&, k, v1, v2

  vXfers = ThisWorkbook.Sheets("Xfers").Range("XferRefs")
  Const sUsrDat$ = ThisWorkbook.Path & "\UserData\"

  For n = LBound(vXfers) To UBound(vXfers)
    On Error Resume Next
GetSrc:
    Set wksSrc = Workbooks(vXfers(n, 1)).Sheets(vXfers(n, 2))
    If wksSrc Is Nothing Then '//file not open
      Workbooks.Open sUsrDat & vXfers(n, 1): GoTo GetSrc
    End If
GetTgt:
    Set wksTgt = Workbooks(vXfers(n, 4)).Sheets(vXfers(n, 5))
    If wksTgt Is Nothing Then
      Workbooks.Open sUsrDat & vXfers(n, 4): GoTo GetTgt
    End If
    Err.Clear: On Error GoTo Cleanup

    v1 = Split(vXfers(n, 3), ","): v2 = Split(vXfers(n, 6), ",')
    For k = LBound(v1) To UBound(v1)
      wksTgt.Range(v2(k)) = Application.Transpose(wksSrc.Range(v1(k)))
    Next 'k
    wksSrc.Parent.Close True: wksTgt.Parent.Close True
  Next 'n

Cleanup:
  Set wksSrc = Nothing: Set wksTgt = Nothing
End Sub 'XferRangeValues

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#109944

FromGS <gs@v.invalid>
Date2017-03-25 22:41 -0400
Message-ID<ob79is$sa2$1@dont-email.me>
In reply to#109943
I recall being able to keep workbooks open if they were next up so I checked 
and found I had revised the 'working' project as follows...


> Sub XferRangeValues()
> ' Transfers range data between multiple workbooks;
> ' Range refs are stored in a dynamic named range on "Xfers" sheet;
> ' Opens/closes workbooks as needed.
> '
>   Dim vXfers, wksSrc As Worksheet, wksTgt As Worksheet
>   Dim n&, k, v1, v2
>
>   vXfers = ThisWorkbook.Sheets("Xfers").Range("XferRefs")
>   Const sUsrDat$ = ThisWorkbook.Path & "\UserData\"
>
>   For n = LBound(vXfers) To UBound(vXfers)
>     On Error Resume Next
> GetSrc:
>     Set wksSrc = Workbooks(vXfers(n, 1)).Sheets(vXfers(n, 2))
>     If wksSrc Is Nothing Then '//file not open
>       Workbooks.Open sUsrDat & vXfers(n, 1): GoTo GetSrc
>     End If
> GetTgt:
>     Set wksTgt = Workbooks(vXfers(n, 4)).Sheets(vXfers(n, 5))
>     If wksTgt Is Nothing Then
>       Workbooks.Open sUsrDat & vXfers(n, 4): GoTo GetTgt
>     End If
>     Err.Clear: On Error GoTo Cleanup
>
>     v1 = Split(vXfers(n, 3), ","): v2 = Split(vXfers(n, 6), ",')
>     For k = LBound(v1) To UBound(v1)
>       wksTgt.Range(v2(k)) = Application.Transpose(wksSrc.Range(v1(k)))
>     Next 'k
    If Not vXfers(n + 1, 1) = vXfers(n, 1) Then wksSrc.Parent.Close True
    If Not vXfers(n + 1, 4) = vXfers(n, 4) Then wksTgt.Parent.Close True
>   Next 'n
>
> Cleanup:
>   Set wksSrc = Nothing: Set wksTgt = Nothing
> End Sub 'XferRangeValues

..and updated the component file accordingly. (I store frm/bas/cls files in a 
"Src" folder for each project. This is where I pulled code for this thread 
from!)

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#109953

FromGS <gs@v.invalid>
Date2017-03-28 12:49 -0400
Message-ID<obe417$rqa$1@dont-email.me>
In reply to#109943
FWIW:
Just thought I'd share this...


The usage scenario that spawned this process is as follows:

  Client-specific tasking to:
    a. distribute updated product templates to field reps;
       This uses a single source file, multiple target files;
       Each field rep's file has an open password, as it also had a 'Notes'
         sheet that usually contained confidential rep-specific info;
       Each field rep accessed their update file via remote login to the 
server.

    b. collect previous period files from field reps;
       Each field rep uploaded their current working file weekly;
         - (this could be during downloading of new templates)
       This uses a single target file, multiple source files.

    c. distribute various sheets from various files to various staff in
         various departments;
       This uses various source/target files.

Notes:
The 'Admin' user has restricted access according to their specific need, and so 
this was managed in the app via login credentials.

Both field reps and office staff use the same addin. Access to functionality is 
controlled via login credentials.

All source/target file require an open password; -this was also stored in the 
appropriate Src/Tgt table listing.

All tasks are menu-driven.

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [standalone]

Page 2 of 2 — ← Prev page 1 [2]

Back to top | Article view | microsoft.public.excel.programming

csiph-web