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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,835
Messages
5,855,907
Members
431,772
Latest member
dannyboi1

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
Top