Re: Array and resize

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Is there a question there?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"CoRrRan" <"CoRrRan~~[at]~~gmail~~[dot]~~com"> wrote in message
news:11dh375iiqh3$.1mrxksfnd6u2w$.dlg@xxxxxxxxxxxxx
> Thanks for the reply Bob,
>
> OK, so using the array function as I did can only be done with a
2D-matrix.
> OK, got that.
>
> I wanted to change my current code, which has about 30/40 rows that
> directly fills an Excel worksheet using a reference cell and the
> '.offset'-function, to a code where I fill a matrix with values and then
> use the 'range().resize()'-method to dump this matrix directly into an
> Excel worksheet (as I am looking for speed enhancements of my code).
>
> ****** previous code *******
> Range("A3").Activate
> With ActiveCell
> .Offset(-2, 0).Value = value1
> .Offset(-1, 0).Value = value2
> .Offset(-1, 7).Value = value3
> ...etc...
> End With
> ****** previous code *******
>
> ****** new code ******
> varMatrix = [{,,,,,,,;,,,,,,,;,,,,,,,;,,,,,,,}]
> Range("A1").Resize(4,8).Value = varMatrix
> ****** new code ******
>
> This 4x8 matrix defines the header of a table, which then gets filled with
> >5000 entries. This 2nd matrix will be filled using two regular
> 'for...to...next' loops, so that shouldn't pose a problem.
>
> CoRrRan
>
> On Tue, 31 May 2005 14:00:38 +0100, Bob Phillips wrote:
>
> > You are not creating a 2D array, but an array containing arrays, and you
> > can't drop these onto a worksheet in this way.
> >
> > You need to load it by dimensions.
> >
> > If you don't have much data, you can use something like
> >
> > varMatrix = [{"1,2,"abc";3,4,"xyz";5,6,"hello"}]
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "CoRrRan" <CoRrRan~~[at]~~gmail~~[dot]~~com> wrote in message
> > news:Xns96679254DC784CoRrRan@xxxxxxxxxxxxxxxxxx
> >> I've got a question regarding Arrays and passing them to an Excel
> >> worksheet.
> >>
> >> Here's the code I try to get to work:
> >>
> >> **************************
> >> Sub DumpValues()
> >>
> >> Dim varMatrix()
> >>
> >> varMatrix = Array(Array(1, 2, 3), _
> >> Array(4, 5, 6), _
> >> Array(7, 8, 9) _
> >> )
> >>
> >> Range("A1").Resize(3, 3).Value = varMatrix
> >>
> >> End Sub
> >> **************************
> >>
> >> Can someone tell me why this doesn't work?
> >>
> >> For the sake of the example, I placed integers into the matrix.
However,
> >> I've got a 4x8 string matrix to dump into an Excel worksheet.
> >>
> >> TIA,
> >> CoRrRan


.

Relevant Pages

  • Re: Average Question
    ... Since my first suggestion used Sumproduct to eliminate an array entry, ... 1/9/09 Bob Alaska $14 ... 1/10/09 Bob Seattle $44 ...
    (microsoft.public.excel)
  • Re: Can someone help me with this slow code
    ... > Bob, perhaps you have a fast 'puter and it goes that quickly. ... > access to Excel. ... > I actually found my own remedy.. ... > array, then compare and alter the array.. ...
    (microsoft.public.excel.programming)
  • Re: excel vba array problem
    ... then use the Splitfunction to create an array of those 11 items, ... > entered in and display them in an excel worksheet. ... > seperate for loop to ask the user 11 times to enter a y value. ...
    (microsoft.public.excel.programming)
  • Re: SMALL FUNCTION - How it works
    ... Bob, didn't have a chance to say thank you for the earlier stuff. ... In Current Sheet!: ... Array is an array or range of numerical data for which you want to ... If n is the number of data points in array, SMALLequals the ...
    (microsoft.public.excel.misc)
  • Re: Looking for a function that will count the first instance of a
    ... "Bob Phillips" wrote: ... are four occurrences of Bob in the array. ... The item that repeats 4 times sums to 1. ... blanks to be counted as a unique item. ...
    (microsoft.public.excel.worksheet.functions)