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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,214,769
Messages
6,121,439
Members
449,032
Latest member
egspen2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top