Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nightmare with Arrays
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nightmare with Arrays
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nightmare with Arrays
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nightmare with Arrays
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nightmare with Arrays
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nightmare with Arrays
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nightmare with Arrays
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nightmare with Arrays
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nightmare with Arrays
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nightmare with Arrays
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nightmare with Arrays
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nightmare with Arrays
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nightmare with Arrays
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nightmare with Arrays
This also works for me.
The only difference I can see is the Dim statements. You learn something every day. Thanks for your help. Best Regards Steve "Gary Keramidas" wrote: 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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nightmare with Arrays
Ah, just seen the ReDim statement. This I think makes the real difference.
My problem here is that i'm not sure how big the array will be. Some more experimenting me thinks. This is a big help though. Cheers Steve "Gary Keramidas" wrote: 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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nightmare with Arrays
there's not enough information to really help. here i showed an example of how
to do it with variables, but i don't know how your data is laid out and how you're determining which columns and rows to use. there may be better ways to do this: Option Explicit Sub test() Dim ws As Worksheet Dim irow As Long Dim lList() As Integer Dim startcol As Long Dim endcol As Long Dim icol, i, rw As Long, x As Long ReDim lList(0) Set ws = Worksheets("Sheet1") startcol = 12 endcol = ws.Cells(2, Columns.Count).End(xlToLeft).Column For irow = 2 To 3 i = 0 For icol = startcol To endcol x = 1 ReDim Preserve lList(0 To endcol - startcol) lList(i) = lList(i) + Worksheets("sheet1").Cells(irow, icol).Value i = i + 1 Next Next End Sub -- Gary "Steve" wrote in message ... Ah, just seen the ReDim statement. This I think makes the real difference. My problem here is that i'm not sure how big the array will be. Some more experimenting me thinks. This is a big help though. Cheers Steve "Gary Keramidas" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif nightmare! | Excel Worksheet Functions | |||
PivotTable Nightmare | Excel Discussion (Misc queries) | |||
Arrays - declaration, adding values to arrays and calculation | Excel Programming | |||
IF Statement nightmare | Excel Discussion (Misc queries) |