Arrays and scattered cell values or another way?

  • Thread starter Thread starter L. Howard
  • Start date Start date
L

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.

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
 
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.
Click to expand...

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:=myRng.Cells.Count) _
= myArr
'Sheets("Sheet2").Range("A1").Resize(rowsize:=myRng.Cells.Count) _
' =worksheetfunction.Transpose(myArr)
End Sub


Regards
Claus B.
 
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
 
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:
Click to expand...

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.
 
Hi Howard,



Am Sat, 15 Feb 2014 20:59:24 +0100 schrieb Claus Busch:








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.

--
Click to expand...

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
 
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:=myRng.Cells.Count) _
= myArr
'Sheets("Sheet2").Range("A1").Resize(rowsize:=myRng.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.Cells.Count) = myArr
End With

End Sub
 
Hi Howard,

Am Sat, 15 Feb 2014 14:56:35 -0800 (PST) schrieb L. Howard:
wksTarget.Range("F1").Resize(columnsize:=myRng.Cells.Count) = myArr
Click to expand...

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.Cells.Count)
is the same as
wksTarget.Range("F1:J1")
so you can write
wksTarget.Range("F1:J1") = myArr


Regards
Claus B.
 
Hi Howard,



Am Sat, 15 Feb 2014 14:56:35 -0800 (PST) schrieb L. Howard:






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.Cells.Count)

is the same as

wksTarget.Range("F1:J1")

so you can write

wksTarget.Range("F1:J1") = myArr





Regards

Claus B.

--
Click to expand...


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.Cells.Count)


Is that correct?

Howard
 
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.Cells.Count)
Click to expand...

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.
 
Hi Howard,



Am Sun, 16 Feb 2014 00:33:42 -0800 (PST) schrieb L. Howard:






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.

--
Click to expand...

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
 
Hi Howard,
Am Sat, 15 Feb 2014 14:56:35 -0800 (PST) schrieb L. Howard:


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.Cells.Count)
is the same as
wksTarget.Range("F1:J1")
so you can write
wksTarget.Range("F1:J1") = myArr
Click to expand...
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
 
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")
 
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")
Click to expand...

???
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
 
???

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
Click to expand...

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.
 
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
 
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
 
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...
 
Back
Top