Copy Array to Array, how to and should I?

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

Sub Fill_Array_This_One works just fine, returns a Value, Text or cell reference from the sheet to range A1:A10.

Sub Fill_Array_That_One is an attempt to gather data from a 'scattered' array (cell references) and copy them to another 'scattered' array on the sheet.
It errors out at the line below '**.

Sub Using_Now works fine and brings me to my questions:
Is there any reason to try to use an Array copied to an Array sub instead of the Using_Now sub?

If so, how do I make Sub Fill_Array_That_One work correctly?

Thanks,
Howard

Option Explicit

Sub Fill_Array_This_One()
' Works okay

Dim myarray As Variant
myarray = Array(1, 2, "Kitty", 4, 5, Range("J2"), 7, 8, "Doggie", Range("N5"))
Range("A1:A10").Select
Range("A1:A10").Value = Application.WorksheetFunction.Transpose(myarray)
End Sub


Sub Fill_Array_That_One()
' ** Does not work

Dim myarray As Variant
Dim a, b, c, d, e, f As Variant

a = Range("J2")
b = Range("J5")
c = Range("L2")
d = Range("L5")
e = Range("N2")
f = Range("N5")

myarray = Array(a, b, c, d, e, f)
'** Wrong number of arguments or Invalid property assignment
Range("B8", "C10", "D12", "E1O", "F8", "D7").Select
Range("B8", "C10", "D12", "E1O", "F8", "D7").Value = Application.WorksheetFunction.Transpose(myarray)
End Sub


Sub Using_Now()
Sheets("Input").Range("F13").Copy Sheets("Data").Range("F3")
Sheets("Input").Range("F16").Copy Sheets("Data").Range("C3")
Sheets("Input").Range("F19").Copy Sheets("Data").Range("D3")
Sheets("Input").Range("I13").Copy Sheets("Data").Range("I3")
Sheets("Input").Range("I16").Copy Sheets("Data").Range("J3")
Sheets("Input").Range("I19").Copy Sheets("Data").Range("M3")
Sheets("Input").Range("L13").Copy Sheets("Data").Range("O3")
Sheets("Input").Range("K17").Copy Sheets("Data").Range("AB3")
Sheets("Input").Range("I13,F13,F16,F19,I19,I16,M13,L13").ClearContents
End Sub
 
Hi Howard,

Am Fri, 14 Jun 2013 06:38:36 -0700 (PDT) schrieb Howard:
Sub Fill_Array_That_One()
' ** Does not work

Dim myarray As Variant
Dim a, b, c, d, e, f As Variant

a = Range("J2")
b = Range("J5")
c = Range("L2")
d = Range("L5")
e = Range("N2")
f = Range("N5")

myarray = Array(a, b, c, d, e, f)
'** Wrong number of arguments or Invalid property assignment
Range("B8", "C10", "D12", "E1O", "F8", "D7").Select
Range("B8", "C10", "D12", "E1O", "F8", "D7").Value = Application.WorksheetFunction.Transpose(myarray)
End Sub
Click to expand...

try:

Sub Fill_Array_That_One()
Dim myarray(6) As Variant
Dim myArr2 As Variant
Dim i As Integer

myarray(0) = Range("J2")
myarray(1) = Range("J5")
myarray(2) = Range("L2")
myarray(3) = Range("L5")
myarray(4) = Range("N2")
myarray(5) = Range("N5")

myArr2 = Array("B8", "C10", "D12", "E10", "F8", "D7")

For i = LBound(myArr2) To UBound(myArr2)
Range(myArr2(i)) = myarray(i)
Next
End Sub


Regards
Claus Busch
 
Sub Fill_Array_That_One()

Dim myarray(6) As Variant

Dim myArr2 As Variant

Dim i As Integer



myarray(0) = Range("J2")

myarray(1) = Range("J5")

myarray(2) = Range("L2")

myarray(3) = Range("L5")

myarray(4) = Range("N2")

myarray(5) = Range("N5")



myArr2 = Array("B8", "C10", "D12", "E10", "F8", "D7")



For i = LBound(myArr2) To UBound(myArr2)

Range(myArr2(i)) = myarray(i)

Next

End Sub

Regards

Claus Busch
Click to expand...

Right on the money!! Thank you Claus.

Howard
 
Same idea as claus', different approach that makes for minimal
maintenance if your range refs change...


<code>
Const sRng1$ = "J2,J5,L2,L5,N2,N5" '//edit to suit
Const sRng2$ = "B8,C10,D12,E10,F8,D7" '//edit to suit

Sub XferVals(Optional sSrc$, Optional sTgt$)
Dim va1, va2, i%

If sSrc = "" Then sSrc = sRng1: If sTgt = "" Then sTgt = sRng2
va1 = Split(sSrc, ","): va2 = Split(sTgt, ",")

For i = LBound(va1) To UBound(va1)
Range(va2(i)).Value = Range(va1(i)).Value
Next 'i
End Sub

...where the array sizes are variable to the num elements in your
delimited strings. Be careful to match the num string elements in both
strings.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Same idea as claus', different approach that makes for minimal

maintenance if your range refs change...

<code>

Const sRng1$ = "J2,J5,L2,L5,N2,N5" '//edit to suit

Const sRng2$ = "B8,C10,D12,E10,F8,D7" '//edit to suit

Sub XferVals(Optional sSrc$, Optional sTgt$)

Dim va1, va2, i%

If sSrc = "" Then sSrc = sRng1: If sTgt = "" Then sTgt = sRng2

va1 = Split(sSrc, ","): va2 = Split(sTgt, ",")

For i = LBound(va1) To UBound(va1)

Range(va2(i)).Value = Range(va1(i)).Value

Next 'i

End Sub

..where the array sizes are variable to the num elements in your

delimited strings. Be careful to match the num string elements in both

strings.

Garry
Click to expand...

Thanks, Gary, that will surely keep me off the streets as I play with your code.

Looks like mostly all I need to fully understand are the two Const sRng1$ and Const sRng2$ and to make sure the elements match.

Perhaps you will have some comments on my latest post about an array to multiple arrays.

Regards,
Howard
 
Same idea as claus', different approach that makes for minimal

maintenance if your range refs change...





<code>

Const sRng1$ = "J2,J5,L2,L5,N2,N5" '//edit to suit

Const sRng2$ = "B8,C10,D12,E10,F8,D7" '//edit to suit



Sub XferVals(Optional sSrc$, Optional sTgt$)

Dim va1, va2, i%



If sSrc = "" Then sSrc = sRng1: If sTgt = "" Then sTgt = sRng2

va1 = Split(sSrc, ","): va2 = Split(sTgt, ",")



For i = LBound(va1) To UBound(va1)

Range(va2(i)).Value = Range(va1(i)).Value

Next 'i

End Sub



..where the array sizes are variable to the num elements in your

delimited strings. Be careful to match the num string elements in both

strings.
Click to expand...

Garry, How do I run this code? I put it a standard module and sheet module but I cannot find a way to execute it.

Howard
 
Garry, How do I run this code? I put it a standard module and sheet
module but I cannot find a way to execute it.

Howard
Click to expand...

To use from other procedures or the macro dialog it needs to be in a
standard module. Sheet modules are for sheet event code. IMO, all other
non-private-to-the-sheet procedures should be place in a standard
module.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
To use from other procedures or the macro dialog it needs to be in a
standard module. Sheet modules are for sheet event code. IMO, all
other non-private-to-the-sheet procedures should be place in a
standard module.
Click to expand...

Oops! I added the optional args at the last minute and so ignore the
above. you need to call this from another procedure...

Sub Test_XferVals()
Call XferVals
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
 
Oops! I added the optional args at the last minute and so ignore the

above. you need to call this from another procedure...



Sub Test_XferVals()

Call XferVals

End Sub
Click to expand...


With "Sub XferVals(Optional sSrc$, Optional sTgt$)" code in module 1, I tried Sub Test_XferVals() both in sheet 1 module and the same standard module 1 the code is in. Nothing happens, so I am wondering if I am overlooking the obvious with the two

Const sRng1$ = "J2,J5,L2,L5,N2,N5" '//edit to suit
Const sRng2$ = "B8,C10,D12,E10,F8,D7" '//edit to suit


Where should they be? I have them preceding the XferVals sub in the standard module 1. How do they know which sheet to refer to for the cells in each line?

Howard
 
Hi Howard,

Am Fri, 14 Jun 2013 13:15:39 -0700 (PDT) schrieb Howard:
With "Sub XferVals(Optional sSrc$, Optional sTgt$)" code in module 1, I tried Sub Test_XferVals() both in sheet 1 module and the same standard module 1 the code is in. Nothing happens, so I am wondering if I am overlooking the obvious with the two

Const sRng1$ = "J2,J5,L2,L5,N2,N5" '//edit to suit
Const sRng2$ = "B8,C10,D12,E10,F8,D7" '//edit to suit

Where should they be? I have them preceding the XferVals sub in the standard module 1. How do they know which sheet to refer to for the cells in each line?
Click to expand...

you have to refer the ranges to the expected sheets.
Here is Garry's code with little changes. Put it in a standard module:

Sub Test()
Dim Arr1 As Variant
Dim Arr2 As Variant
Dim i As Integer

Const Str1 As String = "J2, J5, L2, L5,N2,N5"
Const Str2 As String = "B8, C9, D12, E10, F8, D7"
Arr1 = Split(Str1, ",")
Arr2 = Split(Str2, ",")

For i = LBound(Arr2) To UBound(Arr2)
Sheets("Data").Range(Arr2(i)) = _
Sheets("Input").Range(Arr1(i))
Next
End Sub


Regards
Claus Busch
 
The code runs on the active sheet 'as is'! This is intentional in case
you have more than 1 sheet using the same sub.

The subs/constants go in a standard module. Both constants and XferVals
should be in the same module, but the caller can be anywhere. That will
allow you to run the sub from anywhere via the macros dialog when the
consts contain default range refs. To run sheet-specific refs use a
button on the sheet that calls XferVals as follows...

In the declaration section of the sheet's code module:
Option Explicit

Const msRng1$ = "<sourcerefs>" 'edit to suit
Const msRng2$ = "<targetrefs>" 'edit to suit

In the button's _Click event:

Call XferVals(msRng1, msRng2)

...where msRng1/msRng2 hold the sheet specific cell refs as shown
previously.

Note the following points:
1. XferVals doesn't need to know which sheet is running it because it
will always be the active sheet when you fire the code.

2. Each sheet can optionally use different range refs for sRng1/sRng2,
otherwise the global constants of the same name will be used if args
are left empty. So each sheet can use the sub in the following ways:

<some event code>
Call XferVals '//use global refs
Call XferVals(sSrc:=msRng1) 'use sheet source refs, global target
refs
Call XferVals(sTgt:=msRng2) 'use sheet target refs, global source
refs

3. Constants/variables declared in the declaration section of sheet
code modules are private to that module. Note that my naming convention
persists consistency with the global refs, but have "m" prepended to
them to indicate these have module level scope. So if you use XferVals
with 5 sheets then you can write code for 1st sheet only and copy/paste
to the other 4 without having to make changes.

4. The delimited string for sSrc/sTgt should use the same delimiter
for single cell refs; a different delimiter for array cell refs. You
can hold these in constants as well...

In a standard module:
Const gsDELIMIT_COMMA$ = ","
Const gsDELIMIT_COLON$ = ":"

(Note that I neglected to prepend the "g" identifier to the global
constants in my prior samples<g>. My bad!)

and replace the string literals in code with these...

Sub XferVals2(Optional sSrc$, Optional sTgt$, _
Optional Wks As Worksheet)
Dim va1, va2, i%

If sSrc = "" Then sSrc = sRng1: If sTgt = "" Then sTgt = sRng2
va1 = Split(sSrc, ",")
If InStr(1, sTgt, ":") > 0 _
Then va2 = Split(sTgt, ":") _
Else va2 = Split(sTgt, ",")
If Wks = Nothing Then Set Wks = ActiveSheet

For i = LBound(va1) To UBound(va1)
Range(va2(i)).Value = Range(va1(i)).Value
Next 'i
End Sub

Note also that I modified the args to allow passing a sheet ref. That
means you can pass a sheet ref from anywhere...

In a "Sheet1" event:
Call XferVals(wks:=me) '//use global refs on this sheet

From anywhere:
Call XferVals(Wks:=Sheets("Sheet1")) '//use global refs on "Sheet1"

...meaning you can now have it every which way!<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
 
Option Explicit

Const gsDELIMIT_COMMA$ = ","
Const gsDELIMIT_COLON$ = ":"

Const gsRng1$ = "F13,F16,F19,I13,I16,I19,L13,K17"

Const gsRng2$ = "F3,A3,A20,L5:C3,B5,B20,L6:D3,C7,C20,L7" _
& ":I3,D9,D20,L8:J3,E11,E20,L9:M3,F13,F20,L10" _
& ":O3,G15,G20,L11:P3,H17,H20,L12"


Sub XferVals3(Optional sSrc$, Optional sTgt$, _
Optional Wks As Worksheet)
Dim va1, va2, i%

If sSrc = "" Then sSrc = gsRng1: If sTgt = "" Then sTgt = gsRng2
va1 = Split(sSrc, gsDELIMIT_COMMA)
If InStr(1, sTgt, gsDELIMIT_COLON) > 0 _
Then va2 = Split(sTgt, gsDELIMIT_COLON) _
Else va2 = Split(sTgt, gsDELIMIT_COMMA)
If Wks = Nothing Then Set Wks = ActiveSheet

For i = LBound(va1) To UBound(va1)
Wks.Range(va2(i)).Value = Wks.Range(va1(i)).Value
Next 'i
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
 
Option Explicit



Const gsDELIMIT_COMMA$ = ","

Const gsDELIMIT_COLON$ = ":"



Const gsRng1$ = "F13,F16,F19,I13,I16,I19,L13,K17"



Const gsRng2$ = "F3,A3,A20,L5:C3,B5,B20,L6:D3,C7,C20,L7" _

& ":I3,D9,D20,L8:J3,E11,E20,L9:M3,F13,F20,L10" _

& ":O3,G15,G20,L11:P3,H17,H20,L12"





Sub XferVals3(Optional sSrc$, Optional sTgt$, _

Optional Wks As Worksheet)

Dim va1, va2, i%



If sSrc = "" Then sSrc = gsRng1: If sTgt = "" Then sTgt = gsRng2

va1 = Split(sSrc, gsDELIMIT_COMMA)

If InStr(1, sTgt, gsDELIMIT_COLON) > 0 _

Then va2 = Split(sTgt, gsDELIMIT_COLON) _

Else va2 = Split(sTgt, gsDELIMIT_COMMA)

If Wks = Nothing Then Set Wks = ActiveSheet



For i = LBound(va1) To UBound(va1)

Wks.Range(va2(i)).Value = Wks.Range(va1(i)).Value

Next 'i

End Sub
Click to expand...
Thanks, Garry. This will certainly take some study time on my part.
Thanks for taking the time to explain it all. Hope I can gather some lasting knowledge from this advanced lesson.

Regards,
Howard
 
Back
Top