Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays and scattered cell values or another way?
I want to "gather" the values of many (9 or 10 say) scattered cells in one worksheet and copy them to another workbook to a specific row.
All my searches seem to refer to ranges of cells that are all nicely in a column or a row. I see stuff like this but I cannot find an example where you throw all the scattered cells values in an array and then tell it you want it to go to Workbook XX, Sheet1, Range("B2:K2") and paste the values there. Dim MyArr(1,0) = 1 Dim MyArr(1,1) = 2 Dim MyArr(1,2) = 3 This first example does a nice job of putting 1 to 10 into A1 to A10. Sub Sheet_Fill_Array() Dim myarray As Variant myarray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) Range("A1:A10").Value = Application.WorksheetFunction.Transpose(myarray) End Sub This one I thought would show the values of the cell in myarray one by one in a Msgbox but it errors out. '/ Wrong number of arguments Sub From_sheet_make_array() Dim myarray As Variant myarray = Range("B2", "G2", "B11", "K16", "F17").Value 'Looping structure to look at array. For i = 1 To UBound(myarray) MsgBox myarray(i, 1) Next End Sub Is an array approach the best way to gather all my scattered value and transport then to another workbook? Thanks. Howard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays and scattered cell values or another way?
Hi Howard,
Am Sat, 15 Feb 2014 11:34:09 -0800 (PST) schrieb L. Howard: I want to "gather" the values of many (9 or 10 say) scattered cells in one worksheet and copy them to another workbook to a specific row. you can't copy not adjacent cells. You can loop through the range to assign the values to a new sheet. Or you read the cells of the range in an array: Sub Test() Dim myRng As Range Dim rngC As Range Dim i As Long Dim myArr() As Variant Set myRng = Range("B2,G2,B11,K16,F17") For Each rngC In myRng ReDim Preserve myArr(myRng.Cells.Count - 1) myArr(i) = rngC i = i + 1 Next Sheets("Sheet2").Range("A1").Resize(columnsize:=my Rng.Cells.Count) _ = myArr 'Sheets("Sheet2").Range("A1").Resize(rowsize:=myRn g.Cells.Count) _ ' =worksheetfunction.Transpose(myArr) End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays and scattered cell values or another way?
Another way...
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 -- 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
|
|||
|
|||
Arrays and scattered cell values or another way?
Hi Howard,
Am Sat, 15 Feb 2014 20:59:24 +0100 schrieb Claus Busch: you can't copy not adjacent cells. You can loop through the range to assign the values to a new sheet. Or you read the cells of the range in an array: or without array directly assigned: Set myRng = Sheets("Sheet1").Range("B2,G2,B11,K16,F17") i = 1 For Each rngC In myRng Sheets("Sheet2").Cells(i, 1) = rngC.Value i = i + 1 Next Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays and scattered cell values or another way?
On Saturday, February 15, 2014 12:39:44 PM UTC-8, Claus Busch wrote:
Hi Howard, Am Sat, 15 Feb 2014 20:59:24 +0100 schrieb Claus Busch: you can't copy not adjacent cells. You can loop through the range to assign the values to a new sheet. Or you read the cells of the range in an array: or without array directly assigned: Set myRng = Sheets("Sheet1").Range("B2,G2,B11,K16,F17") i = 1 For Each rngC In myRng Sheets("Sheet2").Cells(i, 1) = rngC.Value i = i + 1 Next Regards Claus B. -- Thanks, Claus and Garry, some good options for sure. I want to go to another workbook and to a single row. Say "Copy WkBook TO.xlsm" sheet 1, Range("F2:K2"). This is from an excerpt from my cheat sheet to copy from one workbook to another. (everything is properly Dimmed) If Not IsFileOpen("C:\Users\Howard Kittle\Documents\Copy WkBook TO.xlsm") Then Workbooks.Open ("C:\Users\Howard Kittle\Documents\Copy WkBook TO.xlsm") End If Set wkbSource = Workbooks("Book10.xlsm") Set wkbTarget = Workbooks("Copy WkBook TO.xlsm") Set wksSource = wkbSource.Sheets("Sheet1") Set wksTarget = wkbTarget.Sheets("Sheet1") With wksSource wkbTarget.range("F2:K2") = ???? End With Howard |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays and scattered cell values or another way?
Well, I got it to work with this.
Thanks Garry and Claus for the help as usual. Glad I was able to hammer it out myself and I appreciate the code work. Regards, Howard Option Explicit Sub Test() Dim myRng 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("B2,G2,B11,K16,F17") For Each rngC In myRng ReDim Preserve myArr(myRng.Cells.Count - 1) myArr(i) = rngC i = i + 1 Next 'Sheets("Sheet2").Range("A1").Resize(columnsize:=m yRng.Cells.Count) _ = myArr 'Sheets("Sheet2").Range("A1").Resize(rowsize:=myRn g.Cells.Count) _ = WorksheetFunction.Transpose(myArr) Set wkbSource = Workbooks("Book1.xlsm") Set wkbTarget = Workbooks("Copy WkBook TO.xlsm") Set wksSource = wkbSource.Sheets("Sheet1") Set wksTarget = wkbTarget.Sheets("Sheet1") With wksSource wksTarget.Range("F1").Resize(columnsize:=myRng.Cel ls.Count) = myArr End With End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays and scattered cell values or another way?
Hi Howard,
Am Sat, 15 Feb 2014 14:56:35 -0800 (PST) schrieb L. Howard: wksTarget.Range("F1").Resize(columnsize:=myRng.Cel ls.Count) = myArr if you know the number of items in your array you can also assign the items to a range wksTarget.Range("F1").Resize(columnsize:=myRng.Cel ls.Count) is the same as wksTarget.Range("F1:J1") so you can write wksTarget.Range("F1:J1") = myArr Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays and scattered cell values or another way?
On Saturday, February 15, 2014 11:36:24 PM UTC-8, Claus Busch wrote:
Hi Howard, Am Sat, 15 Feb 2014 14:56:35 -0800 (PST) schrieb L. Howard: wksTarget.Range("F1").Resize(columnsize:=myRng.Cel ls.Count) = myArr if you know the number of items in your array you can also assign the items to a range wksTarget.Range("F1").Resize(columnsize:=myRng.Cel ls.Count) is the same as wksTarget.Range("F1:J1") so you can write wksTarget.Range("F1:J1") = myArr Regards Claus B. -- Okay, so if hard code five or so cells into myRng likr this Set myRng = Range("B2,G2,B11,K16,F17") Then you can use wksTarget.Range("F1:J1") = myArr But if your code is looking for an unknown or variable row or column length or perhaps looking for all values in UsedRange then you would use this wksTarget.Range("F1").Resize(columnsize:=myRng.Cel ls.Count) Is that correct? Howard |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays and scattered cell values or another way?
Hi Howard,
Am Sun, 16 Feb 2014 00:33:42 -0800 (PST) schrieb L. Howard: Okay, so if hard code five or so cells into myRng likr this Set myRng = Range("B2,G2,B11,K16,F17") Then you can use wksTarget.Range("F1:J1") = myArr But if your code is looking for an unknown or variable row or column length or perhaps looking for all values in UsedRange then you would use this wksTarget.Range("F1").Resize(columnsize:=myRng.Cel ls.Count) yes, that is correct. I prefer the Resize version. For your 5 items I would hard code it without array i= 6 for each rngc in myRng wksTarget.cells(1, i) = rngc i=i+1 next Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays and scattered cell values or another way?
On Sunday, February 16, 2014 12:39:24 AM UTC-8, Claus Busch wrote:
Hi Howard, Am Sun, 16 Feb 2014 00:33:42 -0800 (PST) schrieb L. Howard: Okay, so if hard code five or so cells into myRng likr this Set myRng = Range("B2,G2,B11,K16,F17") Then you can use wksTarget.Range("F1:J1") = myArr But if your code is looking for an unknown or variable row or column length or perhaps looking for all values in UsedRange then you would use this wksTarget.Range("F1").Resize(columnsize:=myRng.Cel ls.Count) yes, that is correct. I prefer the Resize version. For your 5 items I would hard code it without array i= 6 for each rngc in myRng wksTarget.cells(1, i) = rngc i=i+1 next Regards Claus B. -- I see. Goes to cell F1 then increments on over to J giving each cell the next values in rngc. No end to the ways, just tough for me to grab out of the bag when needed. Thanks. Howard |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays and scattered cell values or another way?
Hi Howard,
Am Sat, 15 Feb 2014 14:56:35 -0800 (PST) schrieb L. Howard: wksTarget.Range("F1").Resize(columnsize:=myRng.Cel ls.Count) = myArr if you know the number of items in your array you can also assign the items to a range wksTarget.Range("F1").Resize(columnsize:=myRng.Cel ls.Count) is the same as wksTarget.Range("F1:J1") so you can write wksTarget.Range("F1:J1") = myArr Hmm.., When transfering an array to a worksheet target, I'm inclined to resize to its UBound since the content of the output array may not be the same size as its source range if it got modified during processing. Using UBound covers it nicely! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays and scattered cell values or another way?
How would you use named cells? I tried 4 or five different constructs and can't hit the mark. (worksheet scope)
no no no Set rgRandomCells = myWs.Range(Cells("thisisa1"), Cells("thisisa2"),Cells("thisis23")) Set rgRandomCells = myWs.Range(myWs.Range("thisisa1"), myWs.Range("thisisa1")) Set rgRandomCells = myWs.Range("thisisa1, thisisa2") |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays and scattered cell values or another way?
How would you use named cells? I tried 4 or five different
constructs and can't hit the mark. (worksheet scope) no no no Set rgRandomCells = myWs.Range(Cells("thisisa1"), Cells("thisisa2"),Cells("thisis23")) Set rgRandomCells = myWs.Range(myWs.Range("thisisa1"), myWs.Range("thisisa1")) Set rgRandomCells = myWs.Range("thisisa1, thisisa2") ??? Please explain... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays and scattered cell values or another way?
On Sunday, February 16, 2014 2:00:10 PM UTC-6, GS wrote:
How would you use named cells? I tried 4 or five different constructs and can't hit the mark. (worksheet scope) no no no Set rgRandomCells = myWs.Range(Cells("thisisa1"), Cells("thisisa2"),Cells("thisis23")) Set rgRandomCells = myWs.Range(myWs.Range("thisisa1"), myWs.Range("thisisa1")) Set rgRandomCells = myWs.Range("thisisa1, thisisa2") ??? Please explain... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion Instead of using cell A1 addresses, use the cell NAME. The menu path is -Formulas -Name Manager -New So instead of creating a range containing A1, B4, C8 (for example) it contains NAME_OF_CELL_A1, NAME_OF_CELL_B4. The names are things like TOTAL_HOURS or RATE_OF_PAY ... and so on. |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays and scattered cell values or another way?
I see. No problem if you want the cell addresses 'hard-coded'!
Otherwise, you're missing my point; if you define the range with a name as you suggest then users have to edit that def when adding/removing cells. The point of using a single named cell with a comma delimited list is so users can easily/conveniently edit as desired. In the case of not allowing such editing the cell can be locked, or located in a hidden column/row.<g -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays and scattered cell values or another way?
Hi all
A little function for table and range data I find practical (I'm not sure if it relates to this thread), experts here can customize it further Function CTable(vData) 'This function returns a table of type double 'it allows to convert vData from different type 'or unknown types Dim TableBis() Dim i As Integer 'typename allows to know the datatype Select Case TypeName(vData) Case "Range" 'it's a range ReDim TableBis(1 To vData.Cells.Count) For i = 1 To vData.Cells.Count 'affecter a l'indice i la valeur de la cellule i TableBis(i) = vData.Cells(i) Next i Case "Integer()", "Double()", "Date()", "Variant()" 'it's a table ReDim TableBis(LBound(vData) To UBound(vData)) For i = LBound(vData) To UBound(vData) TableBis(i) = vData(i) Next i Case Else 'it's a number or data, 'it goes into a single variable dimension ReDim TableBis(1 To 1) TableBis(1) = vData End Select CTable = TableBis End Function and some marketing for my small website... http://multiskillz.tekcities.com/ (disable javascript if you don't want to be part of any marketing... website pages will work the same) Pascal |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays and scattered cell values or another way?
just to amend previous post, I'm not it returns a double type, I haven't been running array range script for quite long now, I think vdata can store anything, then the function should handle any type of data back to the sub...
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble with arrays (transferring values between two arrays) | Excel Programming | |||
Scattered Cell entries into one Column | Excel Programming | |||
XL 2007 : Cant move data values (scattered points) in XY Scatter c | Setting up and Configuration of Excel | |||
Arrays - declaration, adding values to arrays and calculation | Excel Programming | |||
Arrays and cell values | Excel Programming |