Hi all
I've had some macros running for a while and thought I knew them inside out. Now I've found a problem, and typically its in a bit of code that someone gave me from this board, and I don't fully understand it.
Basically I fill up an array with some data. I then put the data into a seperate sheet using this bit of code.
Dim output, i As Integer
output = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
For i = LBound(values) To UBound(values)
Cells(65536, output(i)).End(xlUp).Offset(1, 0) = values(i)
Next
ActiveCell.Offset(1, 0).Select
Now it works fine, unless someone leaves a peice of data blank (which will happen quite a lot). Then instead of all the data being left in a one row, it is left in the empty space in each column.
e.g.
1st set of data (1, 1, 1, 1, 1) inputs as 1 - 1 - 1 - 1 - 1
2nd set of data (2, 2, blank, 2, 2) 2 - 2 - 3 - 2 - 2
3rd set of data (3, 3, 3, 3, 3) 3 - 3 - - 3 - 3
Does that make sense?
Obviously the code is taking each bit of the array and finding the next empty space in each column. Whereas I want it to find the next empty space in column 1 (there is always an entry in column 1) and then enter all in the data in that row.
i.e
1 - 1 - 1 - 1 - 1
2 - 2 - - 2 - 2
3 - 3 - 3 - 3 - 3
Would really appreciate some help!!
Thanks for your time, hope it makes sense!!!
Cath
I've had some macros running for a while and thought I knew them inside out. Now I've found a problem, and typically its in a bit of code that someone gave me from this board, and I don't fully understand it.
Basically I fill up an array with some data. I then put the data into a seperate sheet using this bit of code.
Dim output, i As Integer
output = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
For i = LBound(values) To UBound(values)
Cells(65536, output(i)).End(xlUp).Offset(1, 0) = values(i)
Next
ActiveCell.Offset(1, 0).Select
Now it works fine, unless someone leaves a peice of data blank (which will happen quite a lot). Then instead of all the data being left in a one row, it is left in the empty space in each column.
e.g.
1st set of data (1, 1, 1, 1, 1) inputs as 1 - 1 - 1 - 1 - 1
2nd set of data (2, 2, blank, 2, 2) 2 - 2 - 3 - 2 - 2
3rd set of data (3, 3, 3, 3, 3) 3 - 3 - - 3 - 3
Does that make sense?
Obviously the code is taking each bit of the array and finding the next empty space in each column. Whereas I want it to find the next empty space in column 1 (there is always an entry in column 1) and then enter all in the data in that row.
i.e
1 - 1 - 1 - 1 - 1
2 - 2 - - 2 - 2
3 - 3 - 3 - 3 - 3
Would really appreciate some help!!
Thanks for your time, hope it makes sense!!!
Cath