Hi

I did write yesterday but with no real success.

I am trying to put a number of values in an array by looping through some
cells.

I'm having difficulty as I don't know how long the array will be nor can I
sem to add to it.

I have tried the following so far.

A=array() - create an empty array
A(0) = cells(count,1).value
A(1) = cells(count,2).value
etc etc

I have looked at similar code that puts cell information into an array and I
think it's the creation of a blank array that is the problem.

The trouble is that I do not know how big the array is going to be.

Any guidance would be appreciated.

Thanks

Steve

Hi Steve - it's easy once you know how.

sub ArrayDemo()

dim A as string() 'or whatever datatype you want
dim i as integer

for i = 0 to 10
redim preserve A(i)
A(i) = Range("a1").offset(i,0).value
next i

end sub

Redim is the command to change the dimension of the array, preserve means to
keep all the data in there.

Sam

"Steve" wrote:

Hi

I did write yesterday but with no real success.

I am trying to put a number of values in an array by looping through some
cells.

I'm having difficulty as I don't know how long the array will be nor can I
sem to add to it.

I have tried the following so far.

A=array() - create an empty array
A(0) = cells(count,1).value
A(1) = cells(count,2).value
etc etc

I have looked at similar code that puts cell information into an array and I
think it's the creation of a blank array that is the problem.

The trouble is that I do not know how big the array is going to be.

Any guidance would be appreciated.

Thanks

Steve

Perfect

Thanks Sam

"Sam Wilson" wrote:

Hi Steve - it's easy once you know how.

sub ArrayDemo()

dim A as string() 'or whatever datatype you want
dim i as integer

for i = 0 to 10
redim preserve A(i)
A(i) = Range("a1").offset(i,0).value
next i

end sub

Redim is the command to change the dimension of the array, preserve means to
keep all the data in there.

Sam

"Steve" wrote:

Hi

I did write yesterday but with no real success.

I am trying to put a number of values in an array by looping through some
cells.

I'm having difficulty as I don't know how long the array will be nor can I
sem to add to it.

I have tried the following so far.

A=array() - create an empty array
A(0) = cells(count,1).value
A(1) = cells(count,2).value
etc etc

I have looked at similar code that puts cell information into an array and I
think it's the creation of a blank array that is the problem.

The trouble is that I do not know how big the array is going to be.

Any guidance would be appreciated.

Thanks

Steve

Mark it right in case anyone else searches witha similar problem...

"Steve" wrote:

Perfect

Thanks Sam

"Sam Wilson" wrote:

Hi Steve - it's easy once you know how.

sub ArrayDemo()

dim A as string() 'or whatever datatype you want
dim i as integer

for i = 0 to 10
redim preserve A(i)
A(i) = Range("a1").offset(i,0).value
next i

end sub

Redim is the command to change the dimension of the array, preserve means to
keep all the data in there.

Sam

"Steve" wrote:

Hi

I did write yesterday but with no real success.

I am trying to put a number of values in an array by looping through some
cells.

I'm having difficulty as I don't know how long the array will be nor can I
sem to add to it.

I have tried the following so far.

A=array() - create an empty array
A(0) = cells(count,1).value
A(1) = cells(count,2).value
etc etc

I have looked at similar code that puts cell information into an array and I
think it's the creation of a blank array that is the problem.

The trouble is that I do not know how big the array is going to be.

Any guidance would be appreciated.

Thanks

Steve

I say perfect but I have created a test button with the following code:

This throws up a Run time error 13 - Type Mismatch error.

Private Sub CommandButton1_Click()

Dim lList As Variant
Dim iCol, i As Integer

Worksheets("Export").Select

For iCol = 12 To 31

For i = 0 To 20
ReDim Preserve lList(i)
lList(i) = Cells(2, iCol).Value
Next i

Next iCol

End Sub



"Sam Wilson" wrote:

Mark it right in case anyone else searches witha similar problem...

"Steve" wrote:

Perfect

Thanks Sam

"Sam Wilson" wrote:

Hi Steve - it's easy once you know how.

sub ArrayDemo()

dim A as string() 'or whatever datatype you want
dim i as integer

for i = 0 to 10
redim preserve A(i)
A(i) = Range("a1").offset(i,0).value
next i

end sub

Redim is the command to change the dimension of the array, preserve means to
keep all the data in there.

Sam

"Steve" wrote:

Hi

I did write yesterday but with no real success.

I am trying to put a number of values in an array by looping through some
cells.

I'm having difficulty as I don't know how long the array will be nor can I
sem to add to it.

I have tried the following so far.

A=array() - create an empty array
A(0) = cells(count,1).value
A(1) = cells(count,2).value
etc etc

I have looked at similar code that puts cell information into an array and I
think it's the creation of a blank array that is the problem.

The trouble is that I do not know how big the array is going to be.

Any guidance would be appreciated.

Thanks

Steve

Hi Steve,

Perhaps, try:

Dim Arr As Variant

Arr = Range("L1:AE21").Value


---
Regards.
Norman


"Steve" wrote in message
...
I say perfect but I have created a test button with the following code:

This throws up a Run time error 13 - Type Mismatch error.

Private Sub CommandButton1_Click()

Dim lList As Variant
Dim iCol, i As Integer

Worksheets("Export").Select

For iCol = 12 To 31

For i = 0 To 20
ReDim Preserve lList(i)
lList(i) = Cells(2, iCol).Value
Next i

Next iCol

End Sub



"Sam Wilson" wrote:

Mark it right in case anyone else searches witha similar problem...

"Steve" wrote:

Perfect

Thanks Sam

"Sam Wilson" wrote:

Hi Steve - it's easy once you know how.

sub ArrayDemo()

dim A as string() 'or whatever datatype you want
dim i as integer

for i = 0 to 10
redim preserve A(i)
A(i) = Range("a1").offset(i,0).value
next i

end sub

Redim is the command to change the dimension of the array, preserve
means to
keep all the data in there.

Sam

"Steve" wrote:

Hi

I did write yesterday but with no real success.

I am trying to put a number of values in an array by looping
through some
cells.

I'm having difficulty as I don't know how long the array will be
nor can I
sem to add to it.

I have tried the following so far.

A=array() - create an empty array
A(0) = cells(count,1).value
A(1) = cells(count,2).value
etc etc

I have looked at similar code that puts cell information into an
array and I
think it's the creation of a blank array that is the problem.

The trouble is that I do not know how big the array is going to be.

Any guidance would be appreciated.

Thanks

Steve

It should be "dim |List() as variant" , you need the brackets in the
declaration for an array

"Steve" wrote:

I say perfect but I have created a test button with the following code:

This throws up a Run time error 13 - Type Mismatch error.

Private Sub CommandButton1_Click()

Dim lList As Variant
Dim iCol, i As Integer

Worksheets("Export").Select

For iCol = 12 To 31

For i = 0 To 20
ReDim Preserve lList(i)
lList(i) = Cells(2, iCol).Value
Next i

Next iCol

End Sub



"Sam Wilson" wrote:

Mark it right in case anyone else searches witha similar problem...

"Steve" wrote:

Perfect

Thanks Sam

"Sam Wilson" wrote:

Hi Steve - it's easy once you know how.

sub ArrayDemo()

dim A as string() 'or whatever datatype you want
dim i as integer

for i = 0 to 10
redim preserve A(i)
A(i) = Range("a1").offset(i,0).value
next i

end sub

Redim is the command to change the dimension of the array, preserve means to
keep all the data in there.

Sam

"Steve" wrote:

Hi

I did write yesterday but with no real success.

I am trying to put a number of values in an array by looping through some
cells.

I'm having difficulty as I don't know how long the array will be nor can I
sem to add to it.

I have tried the following so far.

A=array() - create an empty array
A(0) = cells(count,1).value
A(1) = cells(count,2).value
etc etc

I have looked at similar code that puts cell information into an array and I
think it's the creation of a blank array that is the problem.

The trouble is that I do not know how big the array is going to be.

Any guidance would be appreciated.

Thanks

Steve

Hi Steve,

You should stay in the original thread.

Try to explain precisely what data is to
be loaded into the array and what you
wish to do with the array.

Note, however, that a range may be
loaded directly into an array, e.g.:

Dim arr As Variant

arr = Range("A1:B10").Value



---
Regards.
Norman


"Steve" wrote in message
...
Hi

I did write yesterday but with no real success.

I am trying to put a number of values in an array by looping through some
cells.

I'm having difficulty as I don't know how long the array will be nor can I
sem to add to it.

I have tried the following so far.

A=array() - create an empty array
A(0) = cells(count,1).value
A(1) = cells(count,2).value
etc etc

I have looked at similar code that puts cell information into an array and
I
think it's the creation of a blank array that is the problem.

The trouble is that I do not know how big the array is going to be.

Any guidance would be appreciated.

Thanks

Steve

Norman

Essentially I want to run across a sheet putting values into an array. Then
move down a line and run across again, this time adding the value to the
value in the array.

e.g.

0 0 0 0 0
1 0 0 1 1
2 0 0 0 2

there are 2 columns to check. I need to do this for each customer in another
list.

I have the following code from Sam which I have put into a test button.
However, this throws up a Run time error 13 - Type Mismatch error.

Private Sub CommandButton1_Click()

Dim lList As Variant
Dim iCol, i As Integer

Worksheets("Export").Select

For iCol = 12 To 31

For i = 0 To 20
ReDim Preserve lList(i)
lList(i) = Cells(2, iCol).Value
Next i

Next iCol

End Sub

"Norman Jones" wrote:

Hi Steve,

You should stay in the original thread.

Try to explain precisely what data is to
be loaded into the array and what you
wish to do with the array.

Note, however, that a range may be
loaded directly into an array, e.g.:

Dim arr As Variant

arr = Range("A1:B10").Value



---
Regards.
Norman


"Steve" wrote in message
...
Hi

I did write yesterday but with no real success.

I am trying to put a number of values in an array by looping through some
cells.

I'm having difficulty as I don't know how long the array will be nor can I
sem to add to it.

I have tried the following so far.

A=array() - create an empty array
A(0) = cells(count,1).value
A(1) = cells(count,2).value
etc etc

I have looked at similar code that puts cell information into an array and
I
think it's the creation of a blank array that is the problem.

The trouble is that I do not know how big the array is going to be.

Any guidance would be appreciated.

Thanks

Steve

As you said, easy when you know how

Thank you

"Steve" wrote:

Norman

Essentially I want to run across a sheet putting values into an array. Then
move down a line and run across again, this time adding the value to the
value in the array.

e.g.

0 0 0 0 0
1 0 0 1 1
2 0 0 0 2

there are 2 columns to check. I need to do this for each customer in another
list.

I have the following code from Sam which I have put into a test button.
However, this throws up a Run time error 13 - Type Mismatch error.

Private Sub CommandButton1_Click()

Dim lList As Variant
Dim iCol, i As Integer

Worksheets("Export").Select

For iCol = 12 To 31

For i = 0 To 20
ReDim Preserve lList(i)
lList(i) = Cells(2, iCol).Value
Next i

Next iCol

End Sub

"Norman Jones" wrote:

Hi Steve,

You should stay in the original thread.

Try to explain precisely what data is to
be loaded into the array and what you
wish to do with the array.

Note, however, that a range may be
loaded directly into an array, e.g.:

Dim arr As Variant

arr = Range("A1:B10").Value



---
Regards.
Norman


"Steve" wrote in message
...
Hi

I did write yesterday but with no real success.

I am trying to put a number of values in an array by looping through some
cells.

I'm having difficulty as I don't know how long the array will be nor can I
sem to add to it.

I have tried the following so far.

A=array() - create an empty array
A(0) = cells(count,1).value
A(1) = cells(count,2).value
etc etc

I have looked at similar code that puts cell information into an array and
I
think it's the creation of a blank array that is the problem.

The trouble is that I do not know how big the array is going to be.

Any guidance would be appreciated.

Thanks

Steve

It should be "dim |List() as variant" , you need the brackets in the
declaration for an array


"Steve" wrote:

Norman

Essentially I want to run across a sheet putting values into an array. Then
move down a line and run across again, this time adding the value to the
value in the array.

e.g.

0 0 0 0 0
1 0 0 1 1
2 0 0 0 2

there are 2 columns to check. I need to do this for each customer in another
list.

I have the following code from Sam which I have put into a test button.
However, this throws up a Run time error 13 - Type Mismatch error.

Private Sub CommandButton1_Click()

Dim lList As Variant
Dim iCol, i As Integer

Worksheets("Export").Select

For iCol = 12 To 31

For i = 0 To 20
ReDim Preserve lList(i)
lList(i) = Cells(2, iCol).Value
Next i

Next iCol

End Sub

"Norman Jones" wrote:

Hi Steve,

You should stay in the original thread.

Try to explain precisely what data is to
be loaded into the array and what you
wish to do with the array.

Note, however, that a range may be
loaded directly into an array, e.g.:

Dim arr As Variant

arr = Range("A1:B10").Value



---
Regards.
Norman


"Steve" wrote in message
...
Hi

I did write yesterday but with no real success.

I am trying to put a number of values in an array by looping through some
cells.

I'm having difficulty as I don't know how long the array will be nor can I
sem to add to it.

I have tried the following so far.

A=array() - create an empty array
A(0) = cells(count,1).value
A(1) = cells(count,2).value
etc etc

I have looked at similar code that puts cell information into an array and
I
think it's the creation of a blank array that is the problem.

The trouble is that I do not know how big the array is going to be.

Any guidance would be appreciated.

Thanks

Steve

And I thought it was all going so well.

Trying to get a routine to add up a number of columns and a number of rows
with the following routine:

Dim lList() As Integer
Dim iCol, i, rw As Integer

For iRow = 2 To 3

i = 0
For iCol = 12 To 13

ReDim Preserve lList(i)
lList(i) = lList(i) + Worksheets("Export").Cells(iRow, iCol).Value
i = i + 1

Next iCol

Next iRow


for some reason it seems to put the first row into teh array and even adds
up the first one of the second row.

This is what I have:

6 3 2 4
1 2 5 7

So my array after the routine should have the following in it:

7 5 7 11

No so luck

Where am i going wrong?

Thanks

Steve


"Sam Wilson" wrote:


It should be "dim |List() as variant" , you need the brackets in the
declaration for an array


"Steve" wrote:

Norman

Essentially I want to run across a sheet putting values into an array. Then
move down a line and run across again, this time adding the value to the
value in the array.

e.g.

0 0 0 0 0
1 0 0 1 1
2 0 0 0 2

there are 2 columns to check. I need to do this for each customer in another
list.

I have the following code from Sam which I have put into a test button.
However, this throws up a Run time error 13 - Type Mismatch error.

Private Sub CommandButton1_Click()

Dim lList As Variant
Dim iCol, i As Integer

Worksheets("Export").Select

For iCol = 12 To 31

For i = 0 To 20
ReDim Preserve lList(i)
lList(i) = Cells(2, iCol).Value
Next i

Next iCol

End Sub

"Norman Jones" wrote:

Hi Steve,

You should stay in the original thread.

Try to explain precisely what data is to
be loaded into the array and what you
wish to do with the array.

Note, however, that a range may be
loaded directly into an array, e.g.:

Dim arr As Variant

arr = Range("A1:B10").Value



---
Regards.
Norman


"Steve" wrote in message
...
Hi

I did write yesterday but with no real success.

I am trying to put a number of values in an array by looping through some
cells.

I'm having difficulty as I don't know how long the array will be nor can I
sem to add to it.

I have tried the following so far.

A=array() - create an empty array
A(0) = cells(count,1).value
A(1) = cells(count,2).value
etc etc

I have looked at similar code that puts cell information into an array and
I
think it's the creation of a blank array that is the problem.

The trouble is that I do not know how big the array is going to be.

Any guidance would be appreciated.

Thanks

Steve

this works for me

Sub test()
Dim irow As Long
Dim lList() As Integer
Dim icol, i, rw As Long

For irow = 2 To 3
i = 0
For icol = 12 To 15
ReDim Preserve lList(0 To 3)
lList(i) = lList(i) + Worksheets("sheet1").Cells(irow, icol).Value
i = i + 1
Next
Next

End Sub


--


Gary


"Steve" wrote in message
...
And I thought it was all going so well.

Trying to get a routine to add up a number of columns and a number of rows
with the following routine:

Dim lList() As Integer
Dim iCol, i, rw As Integer

For iRow = 2 To 3

i = 0
For iCol = 12 To 13

ReDim Preserve lList(i)
lList(i) = lList(i) + Worksheets("Export").Cells(iRow, iCol).Value
i = i + 1

Next iCol

Next iRow


for some reason it seems to put the first row into teh array and even adds
up the first one of the second row.

This is what I have:

6 3 2 4
1 2 5 7

So my array after the routine should have the following in it:

7 5 7 11

No so luck

Where am i going wrong?

Thanks

Steve


"Sam Wilson" wrote:


It should be "dim |List() as variant" , you need the brackets in the
declaration for an array


"Steve" wrote:

Norman

Essentially I want to run across a sheet putting values into an array. Then
move down a line and run across again, this time adding the value to the
value in the array.

e.g.

0 0 0 0 0
1 0 0 1 1
2 0 0 0 2

there are 2 columns to check. I need to do this for each customer in
another
list.

I have the following code from Sam which I have put into a test button.
However, this throws up a Run time error 13 - Type Mismatch error.

Private Sub CommandButton1_Click()

Dim lList As Variant
Dim iCol, i As Integer

Worksheets("Export").Select

For iCol = 12 To 31

For i = 0 To 20
ReDim Preserve lList(i)
lList(i) = Cells(2, iCol).Value
Next i

Next iCol

End Sub

"Norman Jones" wrote:

Hi Steve,

You should stay in the original thread.

Try to explain precisely what data is to
be loaded into the array and what you
wish to do with the array.

Note, however, that a range may be
loaded directly into an array, e.g.:

Dim arr As Variant

arr = Range("A1:B10").Value



---
Regards.
Norman


"Steve" wrote in message
...
Hi

I did write yesterday but with no real success.

I am trying to put a number of values in an array by looping through
some
cells.

I'm having difficulty as I don't know how long the array will be nor
can I
sem to add to it.

I have tried the following so far.

A=array() - create an empty array
A(0) = cells(count,1).value
A(1) = cells(count,2).value
etc etc

I have looked at similar code that puts cell information into an array
and
I
think it's the creation of a blank array that is the problem.

The trouble is that I do not know how big the array is going to be.

Any guidance would be appreciated.

Thanks

Steve