Problem with 'stolen' code!

Cath

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

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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
 
Upvote 0
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
 
Upvote 0
"Arrr...gaaa!"

Bad Cath, bad Cath!

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!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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