Problem with borrowed code

Cath

Board Regular
Joined
Aug 10, 2005
Messages
156
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try:

Code:
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

Board Regular
Joined
Aug 10, 2005
Messages
156
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I couldn't see the Values array in the code you posted so I assumed it was a typo. Silly me. Try:

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

No need for the output array.
 

Cath

Board Regular
Joined
Aug 10, 2005
Messages
156
Brilliant thanks!! I understand it all a bit more now.

Cath
 

Watch MrExcel Video

Forum statistics

Threads
1,118,799
Messages
5,574,384
Members
412,590
Latest member
Velly
Top