Problem with borrowed code

[Cath]

Hi all

sorry to repost but not had any replies from yesterday so thought I'd take another shot at it.

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 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) inputs 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

 

[Andrew Poulsom]

Try:

Option Base 1

Sub Test()
    Dim output
    output = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
    Cells(65536, 1).End(xlUp).Offset(1, 0).Resize(1, UBound(output)) = output
End Sub

 

[Cath]

Hi

It enters 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, etc instead of the data? The data is in an array called 'values' how do I incorporate this??

They're in a staight line though!

Cath

 

[Andrew Poulsom]

I couldn't see the Values array in the code you posted so I assumed it was a typo. Silly me. Try:

Cells(65536, 1).End(xlUp).Offset(1, 0).Resize(1, UBound(Values)) = Values

No need for the output array.

 

[Cath]

Brilliant thanks!! I understand it all a bit more now.

Cath