Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scattered array cells copy to scattered array cells another workbook
The code works fine copying to the resized Range M2 in the target workbook.
wksTarget.Range("M2").Resize(columnsize:=myRng.Cel ls.Count) = myArr I want his array from wksSource Set myRng = Range("A2,A4,R20,C10,N2,O4,F8,H12,G14") To workbook "Copy of long.xlsm" wksTarget.Columns.[8, 6, 4, 5, 3, 7, 2 ,10, 3].End(xlUp)(2) = myArr (translated to workable code of course) Thanks. Howard Sub AbookToLong() Dim myRng As Range, MyRng1 As Range Dim rngC As Range Dim i As Long Dim myArr() As Variant Dim wksSource As Worksheet, wksTarget As Worksheet Dim wkbSource As Workbook, wkbTarget As Workbook Dim rngSource As Range, rngTarget As Range Set myRng = Range("A2,A4,R20,C10,N2,O4,F8,H12,G14") Set wkbSource = Workbooks("Array cells to another workbook.xlsm") Set wkbTarget = Workbooks("Copy of long.xlsm") Set wksSource = wkbSource.Sheets("Sheet1") Set wksTarget = wkbTarget.Sheets("Sheet1") Application.ScreenUpdating = False For Each rngC In myRng ReDim Preserve myArr(myRng.Cells.Count - 1) myArr(i) = rngC i = i + 1 Next With wksSource 'wksTarget.Range("M2").Resize(columnsize:=myRng.Ce lls.Count) = myArr wksTarget.Columns.[8, 6, 4, 5, 3, 7, 2 ,10, 3] = myArr End With 'wksSource.Range("C7:C18").Copy ' wksTarget.Range("X2").PasteSpecial Transpose:=True 'wksSource.Range("C33:C50").Copy ' wksTarget.Range("AJ2").PasteSpecial Transpose:=True Application.ScreenUpdating = False End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scattered array cells copy to scattered array cells another workbook
I tried this for the destination and it enters "A2" in all cells. The destination cells are on a compact slant for easy checking. I can't figure out what it is telling me by doing that...? With wksSource 'wksTarget.Range("M2").Resize(columnsize:=myRng.Ce lls.Count) = myArr wksTarget.Range("A2,B3,C4,D5,E6,F7,G8,H9,I10") = myArr End With I should mention perhaps that the values in every cell is also the cell address. On the sheet cell A2 has "A2" in it and the same for all the rest, the cell holds it own address. I thought that made sense until it started to confuse me. Howard |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scattered array cells copy to scattered array cells another workbook
The code works fine copying to the resized Range M2 in the target
workbook. wksTarget.Range("M2").Resize(columnsize:=myRng.Cel ls.Count) = myArr I want his array from wksSource Set myRng = Range("A2,A4,R20,C10,N2,O4,F8,H12,G14") To workbook "Copy of long.xlsm" wksTarget.Columns.[8, 6, 4, 5, 3, 7, 2 ,10, 3].End(xlUp)(2) = myArr (translated to workable code of course) Thanks. Howard Sub AbookToLong() Dim myRng As Range, MyRng1 As Range Dim rngC As Range Dim i As Long Dim myArr() As Variant Dim wksSource As Worksheet, wksTarget As Worksheet Dim wkbSource As Workbook, wkbTarget As Workbook Dim rngSource As Range, rngTarget As Range Set myRng = Range("A2,A4,R20,C10,N2,O4,F8,H12,G14") This refs the current active sheet! Set wkbSource = Workbooks("Array cells to another workbook.xlsm") Set wkbTarget = Workbooks("Copy of long.xlsm") Set wksSource = wkbSource.Sheets("Sheet1") Set wksTarget = wkbTarget.Sheets("Sheet1") Application.ScreenUpdating = False For Each rngC In myRng ReDim Preserve myArr(myRng.Cells.Count - 1) myArr(i) = rngC i = i + 1 Next With wksSource 'wksTarget.Range("M2").Resize(columnsize:=myRng.Ce lls.Count) = myArr wksTarget.Columns.[8, 6, 4, 5, 3, 7, 2 ,10, 3] = myArr End With I don't understand using a With clause here since nothing is reffing anything on wksSource! 'wksSource.Range("C7:C18").Copy ' wksTarget.Range("X2").PasteSpecial Transpose:=True 'wksSource.Range("C33:C50").Copy ' wksTarget.Range("AJ2").PasteSpecial Transpose:=True Application.ScreenUpdating = False 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scattered array cells copy to scattered array cells another workbook
Hi Garry,
Here is the example/s I'm working off of, includes three from you which I have not been able to figure how to ...Dump the array into the target sheet. In AbookToLong code the only thing I am trying to do is use the commented out line instead of the resize M2 line to dump to the target workbook. I failed to note it but I am sure Claus wrote this one. Seems to me that dumping an array to cells that are not all together in a row or a column is quite difficult. You were helping me once on this and I was able to persuade the OP to revamp the worksheet to accept the tradional array dump. I'm back with a need to do dump to 'scattered columns/cells' if practical. If not, I will pass it on as impractical or not code worthy or whatever. Howard Sub AbookToLong() Dim myRng As Range, MyRng1 As Range Dim rngS As Range, rngD As Range Dim i As Long Dim myArr() As Variant Dim wksSource As Worksheet, wksTarget As Worksheet Dim wkbSource As Workbook, wkbTarget As Workbook Dim rngSource As Range, rngTarget As Range Set myRng = Range("A2,A4,R20,C10,N2,O4,F8,H12,G14") Set wkbSource = Workbooks("Array cells to another workbook.xlsm") Set wkbTarget = Workbooks("Copy of long.xlsm") Set wksSource = wkbSource.Sheets("Sheet1") Set wksTarget = wkbTarget.Sheets("Sheet1") Application.ScreenUpdating = False For Each rngS In myRng ReDim Preserve myArr(myRng.Cells.Count - 1) myArr(i) = rngS i = i + 1 Next With wksSource wksTarget.Range("M2").Resize(columnsize:=myRng.Cel ls.Count) = myArr 'This is the non working attempt by me 'wksTarget.Range("A2,B3,C4,D5,E6,F7,G8,H9,I10") = myArr End With '//*****Ignore these commeted out lines******// 'wksSource.Range("C7:C18").Copy ' wksTarget.Range("X2").PasteSpecial Transpose:=True 'wksSource.Range("C33:C50").Copy ' wksTarget.Range("AJ2").PasteSpecial Transpose:=True Application.ScreenUpdating = False End Sub 'Another way... '/ Garry Sub MoveScatteredValues() Dim v, vaMyVals(), iIncr% Const sRngRefs$ = "B2,G2,B11,K16,F17" For Each v In Split(sRngRefs, ",") ReDim Preserve vaMyVals(iIncr) vaMyVals(iIncr) = Range(v).Value iIncr = iIncr + 1 Next 'v 'Dump the array into the target sheet '... End Sub '-OR- '..if the range addresses were stored in a named range... Sub MoveScatteredValues2() Dim v, vaMyVals(), iIncr% For Each v In Split(Range("RngRefs").Value, ",") ReDim Preserve vaMyVals(iIncr) vaMyVals(iIncr) = Range(v).Value iIncr = iIncr + 1 Next 'v 'Dump the array into the target sheet '... End Sub '-OR- '..if the range addresses are not just single cells, then a modified 'version of Claus' idea... 'Range("RngRefs").Value: "B2,G2,B11:F11,K16,F17" Sub MoveScatteredValues3() Dim c As Range, sRefs$, vaMyVals(), iIncr% sRefs = Range("RngRefs").Value For Each c In Range(sRefs) ReDim Preserve vaMyVals(iIncr) vaMyVals(iIncr) = Range(c).Value iIncr = iIncr + 1 Next 'c 'Dump the array into the target sheet '... End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scattered array cells copy to scattered array cells another workbook
Fixing yours so it works as expected...
Sub AbookToLong() Dim myRng As Range, rngS As Range Dim i As Long, myArr() As Variant Dim wksSource As Worksheet, wksTarget As Worksheet Dim wkbSource As Workbook, wkbTarget As Workbook Set wkbSource = Workbooks("Array cells to another workbook.xlsm") Set wkbTarget = Workbooks("Copy of long.xlsm") Set wksSource = wkbSource.Sheets("Sheet1") Set wksTarget = wkbTarget.Sheets("Sheet1") Set myRng = wksSource.Range("A2,A4,R20,C10,N2,O4,F8,H12,G14") Application.ScreenUpdating = False ReDim myArr(myRng.Cells.Count - 1) For Each rngS In myRng myArr(i) = rngS.Value: i = i + 1 Next Set myRng = wksTarget.Range("A2,B3,C4,D5,E6,F7,G8,H9,I10") i = 0 For Each rngS In myRng rngS.Value = myArr(i): i = i + 1 Next Application.ScreenUpdating = False End Sub ...and how I'd do it... Sub AbookToLong2() Dim wksSource As Worksheet, wksTarget As Worksheet Dim wkbSource As Workbook, wkbTarget As Workbook Dim n&, v1, v2 Set wkbSource = Workbooks("Array cells to another workbook.xlsm") Set wkbTarget = Workbooks("Copy of long.xlsm") Set wksSource = wkbSource.Sheets("Sheet1") Set wksTarget = wkbTarget.Sheets("Sheet1") v1 = Split("A2,A4,R20,C10,N2,O4,F8,H12,G14", ",") v2 = Split("A2,B3,C4,D5,E6,F7,G8,H9,I10", ",") For n = LBound(v1) To UBound(v1) wksTarget.Range(v2(n)) = wksSource.Range(v1(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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scattered array cells copy to scattered array cells another workbook
Thanks Garry, anxious to try them both.
Will post back. Howard |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scattered array cells copy to scattered array cells another workbook
Hi Howard,
Am Sat, 12 Jul 2014 16:25:04 -0700 (PDT) schrieb L. Howard: wksTarget.Range("M2").Resize(columnsize:=myRng.Cel ls.Count) = myArr I want his array from wksSource Set myRng = Range("A2,A4,R20,C10,N2,O4,F8,H12,G14") To workbook "Copy of long.xlsm" wksTarget.Columns.[8, 6, 4, 5, 3, 7, 2 ,10, 3].End(xlUp)(2) = myArr (translated to workable code of course) if I understand you correctly try (the code is in wksSource): Sub ABookToLong() Dim wksSource As Worksheet, wksTarget As Worksheet Dim arrCells() As Variant, arrCols As Variant Dim myRng As Range, rngC As Range Dim strCols As String Dim i As Long Set wksSource = ThisWorkbook.Sheets("Sheet1") Set wksTarget = Workbooks("Copy of long.xlsm").Sheets("Sheet1") Set myRng = wksSource.Range("A2,A4,R20,C10,N2,O4,F8,H12,G14") strCols = "8,6,4,5,3,7,2,10,3" arrCols = Split(strCols, ",") For Each rngC In myRng ReDim Preserve arrCells(myRng.Cells.Count - 1) arrCells(i) = rngC i = i + 1 Next For i = LBound(arrCols) To UBound(arrCols) wksTarget.Cells(Rows.Count, CInt(arrCols(i))) _ .End(xlUp)(2) = arrCells(i) Next End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scattered array cells copy to scattered array cells another workbook
Hi Garry,
Both work very well. On mine I was able to add the following to produce a list. For Each rngS In myRng rngS.Range("A" & Cells(Rows.Count, "A").End(xlUp).Row) _ .End(xlUp)(2).Value = myArr(i): i = i + 1 Next Your code is really compact, and clean, if that's the word. I tried to do the same on yours, but can't find the combination to make it work. Tried slipping .End(xlUp)(2) in a couple of places but no luck. Would be nice to have both a specific destination and a listing option also. Nice nuggets of code, as always. Thanks much. Howard |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scattered array cells copy to scattered array cells another workbook
Hi Claus,
Many thanks, works very well. Just plug-and-play. Appreciate the help. Regards, Howard |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scattered array cells copy to scattered array cells another workbook
Hi Garry,
Both work very well. On mine I was able to add the following to produce a list. For Each rngS In myRng rngS.Range("A" & Cells(Rows.Count, "A").End(xlUp).Row) _ .End(xlUp)(2).Value = myArr(i): i = i + 1 Next Your code is really compact, and clean, if that's the word. I tried to do the same on yours, but can't find the combination to make it work. Tried slipping .End(xlUp)(2) in a couple of places but no luck. Would be nice to have both a specific destination and a listing option also. Nice nuggets of code, as always. Thanks much. Howard Just put the data in an array and 'dump' the array where you want the list to go. If you use a 1D array and want a col list then transpose it! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scattered array cells copy to scattered array cells another workbook
On Sunday, July 13, 2014 6:28:39 PM UTC-7, GS wrote:
Hi Garry, Both work very well. On mine I was able to add the following to produce a list. For Each rngS In myRng rngS.Range("A" & Cells(Rows.Count, "A").End(xlUp).Row) _ .End(xlUp)(2).Value = myArr(i): i = i + 1 Next Your code is really compact, and clean, if that's the word. I tried to do the same on yours, but can't find the combination to make it work. Tried slipping .End(xlUp)(2) in a couple of places but no luck. Would be nice to have both a specific destination and a listing option also. Nice nuggets of code, as always. Thanks much. Howard Just put the data in an array and 'dump' the array where you want the list to go. If you use a 1D array and want a col list then transpose it! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion I'll give it a go. Thanks again. Howard |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scattered array cells copy to scattered array cells another workbook
Hi Howard,
Am Sun, 13 Jul 2014 17:36:31 -0700 (PDT) schrieb L. Howard: Many thanks, works very well. Just plug-and-play. glad to help. Thank you for the feedback. If you like to write the target columns with letters try: Sub ABookToLong2() Dim wksSource As Worksheet, wksTarget As Worksheet Dim arrCells() As Variant, arrCols As Variant Dim myRng As Range, rngC As Range Dim strCols As String Dim i As Long Set wksSource = ThisWorkbook.Sheets("Sheet1") Set wksTarget = Workbooks("Copy of long.xlsm").Sheets("Sheet1") Set myRng = wksSource.Range("A2,A4,R20,C10,N2,O4,F8,H12,G14") strCols = "H,F,D,E,C,G,B,J,C" arrCols = Split(strCols, ",") For Each rngC In myRng ReDim Preserve arrCells(myRng.Cells.Count - 1) arrCells(i) = rngC i = i + 1 Next For i = LBound(arrCols) To UBound(arrCols) wksTarget.Cells(Rows.Count, Asc(UCase(arrCols(i))) - 64) _ .End(xlUp)(2) = arrCells(i) Next End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scattered array cells copy to scattered array cells another workbook
On Sunday, July 13, 2014 10:33:49 PM UTC-7, Claus Busch wrote:
Hi Howard, Am Sun, 13 Jul 2014 17:36:31 -0700 (PDT) schrieb L. Howard: Many thanks, works very well. Just plug-and-play. glad to help. Thank you for the feedback. If you like to write the target columns with letters try: That's quite handy, saves having to count the columns when one gets over to the double (and triple) column headings. Appreciate that. Howard |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scattered array cells copy to scattered array cells another workbook
Hi Howard,
Am Mon, 14 Jul 2014 01:55:57 -0700 (PDT) schrieb L. Howard: That's quite handy, saves having to count the columns when one gets over to the double (and triple) column headings. that is only working for 1 digit column headers. If you have 2 digits headers you have to change the code: Sub ABookToLong2() Dim wksSource As Worksheet, wksTarget As Worksheet Dim arrCells() As Variant, arrCols As Variant Dim myRng As Range, rngC As Range Dim strCols As String Dim i As Long, ColNr As Long Set wksSource = ThisWorkbook.Sheets("Sheet1") Set wksTarget = Workbooks("Copy of long.xlsm").Sheets("Sheet1") Set myRng = wksSource.Range("A2,A4,R20,C10,N2,O4,F8,H12,G14") strCols = "H,F,D,E,C,G,B,J,CA" arrCols = Split(strCols, ",") For Each rngC In myRng ReDim Preserve arrCells(myRng.Cells.Count - 1) arrCells(i) = rngC i = i + 1 Next For i = LBound(arrCols) To UBound(arrCols) If Len(arrCols(i)) = 1 Then ColNr = Asc(UCase(arrCols(i))) - 64 ElseIf Len(arrCols(i)) = 2 Then ColNr = (Asc(Left(arrCols(i), 1)) - 64) * _ 26 + Asc(Right(arrCols(i), 1)) - 64 End If wksTarget.Cells(Rows.Count, ColNr).End(xlUp)(2) = arrCells(i) Next End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scattered array cells copy to scattered array cells another workbook
On Monday, July 14, 2014 3:02:43 AM UTC-7, Claus Busch wrote:
Hi Howard, Am Mon, 14 Jul 2014 01:55:57 -0700 (PDT) schrieb L. Howard: That's quite handy, saves having to count the columns when one gets over to the double (and triple) column headings. that is only working for 1 digit column headers. If you have 2 digits headers you have to change the code: Aha! Bad assumption on my part, I did not test. Thanks for code code AND the heads up. Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying cells from on row in one wkbk to scattered cells in anthr | Excel Programming | |||
Best way to total scattered data? | Excel Discussion (Misc queries) | |||
How do I sum scattered entries on an Excel worksheet? | Excel Worksheet Functions | |||
Checking for Empty Scattered Cells | Excel Programming | |||
Labelling at xy scattered chart | Charts and Charting in Excel |