# Problem with 'stolen' code!

#### Cath

##### Board Regular
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

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
More details...

Hi Cath,

maybe you should empty the array? With the sniplets of code you supplied that's hard to determine. Could you maybe post some longer piece with some explaining?

Greetz,

Koen

You may try doing a loop on the data range, first, to test for Blanks and/or Nulls and if a cell in that range is missing data add a zero or whatever you consider a default value?

Set myRng = Range("Whatever")

For Each cell In myRng
If (cell.Value = "" Or cell.Value = xlNull) Then cell.Value = 0
Next cell

"Arrr...gaaa!"

Keep to one posting, add a comment to it to bump it back up, but don't start a new one, unless you change topics!

sorry!!

Replies
1
Views
88
Replies
4
Views
330
Replies
4
Views
172
Replies
2
Views
107
Replies
5
Views
263

1,196,288
Messages
6,014,495
Members
441,823
Latest member
GregG26

### 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.

### Which adblocker are you using?

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

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