Macro Wanted VBA Table to List with more than 3 columns

**carpediem_3112** · 01-17-2013, 09:17 PM

Howdy,

I have a table that shows a set of possible combinations, but in order to actually use it, I need to make it a list.

The table is like
Sample Table Origin.gif

And the wanted result is like
Sample List RESULT.gif

Which means,
FIRST: Several columns (SKU, CODE, DESCRIPTION), making a "three column row"
THEN: If you have a number 1-one, you make de combination of the column TITLE (red) with the "three column row" PLUS the second column TITLE (blue)

Which means that each table ROW, can make up to 4 LIST rows in this example (1:4).

Real life, I have "six column row" 9 reds (and growing) and 16 blues (and growing), giving a good 1:144 :-S

So far, I've found how to make, from the origin table a 3 column list (NOT WANTED)
Sample List NOT wanted result.gif
(NOT WANTED)

The above (not wanted) result came from http://michiel.wordpress.com/2009/03...bles-to-lists/

Please Login or Register  to view this content.

Any ideas??

As required, you can find the sample xls here http://www.sendspace.com/file/pjen9x

**rylo** · 01-17-2013, 10:05 PM

Hi

rather than pictures, attach an example workbook with 2 sheets - a before and after view.

rylo

**carpediem_3112** · 01-17-2013, 10:27 PM

Sample uploaded on orignal post.

Actually have 3 sheets, because the macro is included, and the 3rd sheet is the result of the macro.

**rylo** · 01-17-2013, 11:33 PM

Hi

the code below will produce the required output from the example file provided. Make sure you add a new sheeet called sheet1, select the sheet Origin and run the code below.

Please Login or Register  to view this content.

How do you want your warning for the non output results to be given? Output to another part of the sheet? Messagebox?

**wayusei** · 01-17-2013, 11:52 PM

Hi, I'm trying to learn a little bit more of VBA and I have a couple of questions that I'd love to have them answered.

1. why didn't you declare the outrow variable?
2. Can you explain point by point the outrow statement?
OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

I hope this doesn't seem too much, but i'd thank you if you could answer me.

Thanks!

**rylo** · 01-18-2013, 12:07 AM

Wayusei

1) Being lazy and relying on Excel default.
2)
Outsh is a variable that I've set to be the output sheet sheet1. Really a shortcut
cells(rows.count,1) - this effectively finds the cell in column A. It is dynamic in that if you have excel 2003 it will go to A65531, Excel 2007 then A1048576 etc
.end(xlup) - the same as doing a ctrl up arrow from the last cell in column A. It will then find the last used cell in column A
.offset(1,0) - goes down 1 cell - effectively the next blank cell in column A
.row - the row number of that cell
So this block brings back the next available row in column A and puts it in the variable outrow.

I prefer coming from the bottom up to get the next available cell rather than the top down as the latter may not cover off any blank cells that are lurking about

HTH

rylo

**wayusei** · 01-18-2013, 12:12 AM

Thanks a million that Was really helpful. I hope someday to write vba code as simple as this. Thanks!

**carpediem_3112** · 01-18-2013, 11:17 AM

You are a Guru RYLO!!!

Works like a Charm!

When I'm done writting my final code, I'll be back to upload it.

Very much tank you!!

**carpediem_3112** · 01-18-2013, 12:50 PM

And for the warning, I want FIRST the macro to check if some info won't be displayed because you can have all 0's below RED OR BLUE.

And as aditional rule, the blue are EXCLUSIVE.

Actually, just started a new thread
http://www.excelforum.com/excel-prog...anted-vba.html

Thank you very much!