Excel VBA Array Question 2

[Jdoggers]

Hi,
I am trying to take a set of values from a spreadsheet and go from memory to the spreadsheet and spreadsheet to memory. Suppose we take an already made array of values...lets start with a one dimensional array of like 20 elements. We'll call it myarray(1 to 20). Is there any way to put the values from myarray(1) to myarray(20) from memory to the spreadsheet, and the reverse going from row A1 to A20 and put those values into memory. The only thing is that i wanted to do this without using a loop that goes:

dim x as integer
dim myarray(1 to 20) as double

for i = 1 to 20
worksheets(1).range("A" & i).value = myarray(i)
next i


this method is a simple solution, but in my question, i am asking if there is any way to do something like

worksheets(1).range("A1:A20").values = myarray

or something like the reverse where you go

myarray = worksheets(1).range("A1:A20").values

this code would do the whole array in one big statement but i just dont know the syntax of it. Also, this method would be much faster than the loop version earlier. Anyone got any ideas?

thanks

 

[SkipVought]

Hi,

The syntax is in a different galaxcy in a different time.

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[TonyJollans]

Pay no attention to Skip

Another galaxy isn't enough, another dimension is what you need!

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Zathras]

Looks like both Skip and Tony are getting a head start on the Fourth (or is that the fifth?)

Remember that a spreadsheet is 2-dimensional so...

You need to use a variant and 2-dimension arrays. Play around with this code and you should be able to get the idea:

Option Base 1

Sub Test()
Dim r As Range
Dim MyArray(20, 1) As Double
Dim MySecondArray
  Set r = Range("A1:A20")[COLOR=green]
  ' Loop for initializing array only[/color]
  For i = 1 To 20
    MyArray(i, 1) = 100 / i
  Next i
  
  r = MyArray
  MySecondArray = r
  MsgBox MySecondArray(12, 1)
End Sub

 

[SkipVought]

uh uh uh uh uh

without using a loop

!!!!!!!!!

Orders from the galactic overlord!

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[Jdoggers]

hey guys,

i figured out that if you wanted to copy a range of cells from one range to another, it is much faster to use a variant. Suppose i want to copy a1 to c1000 and put it in d1 to f1000 in the code below:

dim myvar as variant
dim bottom as long

bottom = 1000

myvar = Range("A1:C" & bottom).Value
Range("D1:F" & bottom).Value = myvar

but the only thing is that when i use this method, if i wanted to perform an operation on the variant, how could i do something like the following:

Range("D1:F" & bottom).Value = myvar * 2

This syntax is not working. Does anyone know how to perform operations on a variant?

thanks

 

[TonyJollans]

Hi Jdoggers,

You don't need to use intermediate variables to copy one range to another. And, as you can't perform operations on arrays, but can (using Paste Special) on ranges of cells, you don't even want to ..

Dim myvar As Variant
Dim bottom As Long

bottom = 10

Range("D1:F" & bottom).Value = 2
Range("A1:C" & bottom).Copy
Range("D1:F" & bottom).PasteSpecial Operation:=xlMultiply
Application.CutCopyMode = False

If you actually wanted to perform an operation of this sort on a array, you might well be best doing it via a worksheet, but as you want it on the worksheet anyway just drop the array.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Jdoggers]

Hi tony,
I was wondering if there was a way to pick out a value that is in a variant type variable...similar to taking a specific value in an array, and performing an operation only on that specific place in the array, or variant. By the way, what is a variant...is it similar to an array, or is it a type of variable that can hold any type of data??? its kind of confusing. Anyway, thanks for the help

 

[PHV]

what is a variant
When in VBE with the cursor inside the word Variant press the F1 key.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886