VBA create a List from selected inputs on a seperate worksheet

[Gurnek]

All,

I am trying to create a macro which creates a list on a seperate worksheet.

The data i have is arranged in different cells in "Sheet 1" but predominantly looks like:

[TABLE="width: 127"]
<tbody>[TR]
[TD]Room
[/TD]
[TD]No.
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lecture Rooms
[/TD]
[TD="align: right"]12
[/TD]
[/TR]
[TR]
[TD]Seminar Rooms
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]Dining Hall
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Sports Hall
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Activity Rooms
[/TD]
[TD="align: right"]4
[/TD]
[/TR]
</tbody>[/TABLE]


What i am hoping to achieve is a list in the following format but in a different spreadsheet:

[TABLE="width: 160"]
<tbody>[TR]
[TD]Lecture Rooms
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Lecture Rooms
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Lecture Rooms
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]Lecture Rooms
[/TD]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD]Lecture Rooms
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD]Lecture Rooms
[/TD]
[TD="align: right"]6
[/TD]
[/TR]
[TR]
[TD]Lecture Rooms
[/TD]
[TD="align: right"]7
[/TD]
[/TR]
[TR]
[TD]Lecture Rooms
[/TD]
[TD="align: right"]8
[/TD]
[/TR]
[TR]
[TD]Lecture Rooms
[/TD]
[TD="align: right"]9
[/TD]
[/TR]
[TR]
[TD]Lecture Rooms
[/TD]
[TD="align: right"]10
[/TD]
[/TR]
[TR]
[TD]Lecture Rooms
[/TD]
[TD="align: right"]11
[/TD]
[/TR]
[TR]
[TD]Lecture Rooms
[/TD]
[TD="align: right"]12
[/TD]
[/TR]
[TR]
[TD]Seminar Rooms
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Seminar Rooms
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Seminar Rooms
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]Dining Hall
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Dining Hall
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Sports Hall
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Sports Hall
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Activity Rooms
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Activity Rooms
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Activity Rooms
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]Activity Rooms
[/TD]
[TD="align: right"]4
[/TD]
[/TR]
</tbody>[/TABLE]

The list can be generated at any point so i think the start of the macro needs to reset the list produced.


Any help is appreciated.

Regards,


Gurnek.

 

[SteveO59L]

and is sheet2 the name of your sheet?, i.e not sheet 2

 

[Gurnek]

Thats right..

 

[SteveO59L]

so could you do all of the work in the same sheet?

Dim c As Range, rng
Dim count_val As Integer
Dim room As String
Dim room_no As Integer

Dim lastrow As Long
Set rng = Range("a2:a6")
Range("e2:f1000").ClearContents
For Each c In rng
room = c.Value
room_no = 1

count_val = c.Offset(0, 1).Value


lastrow = Cells(Rows.Count, "e").End(xlUp).Row + 1
Do Until count_val = 0
Range("e" & lastrow).Value = room
Range("f" & lastrow).Value = room_no
room_no = room_no + 1

count_val = count_val - 1
lastrow = Cells(Rows.Count, "e").End(xlUp).Row + 1
Loop

Next c

 

[alansidman]

I am unable to recreate the error you point out. The code runs for me and presents me with the output you have described exactly.

 

[Gurnek]

No problem Alan,

I have used Steve's solution and made a few tweeks to make it work in a new sheet with some conditional fomatting.

Many Thanks for the help, it is much appreciated.

Regards,

Gurnek.

 

[Gurnek]

Steve - Many thanks for this solution, it worked a treat!!

Is it possible (using the same sort of method as above) to do the reverse so:

Room 1
Room 2
Room 3
Dining Room 1
Dining Room 2

to then be matched with a same room description (Room and Dining Room) and then using the Offset, be populate the number of rooms into cells into a specific cell like this:

Room 3
Dining Room 2

Hope that makes some sense.

Regards,

Gurnek.