VBA array exercise

[dkohli]

Use the data in the ‘Data’ worksheet of the workbook “VBA Training Exercise 2” (attached image) and segregate it on the basis of ‘Type’. Please follow the below points:

1. Find unique ‘Types’ of Roller coasters in the given data.
2. Add new worksheets in the same workbook and name them on the type of Roller Coaster. (So based on the current data, you will have to create three sheets by the name of unique ‘Type’ - Alloys, Wood and Steel).
3. Each sheet should contain the Roller Coaster and Amusement Park name according to the type of roller coaster. For e.g. the ‘Alloys’ sheet should contain Roller Coaster – Air and Amusement Park – Alton Towers and so on, as shown in the below table -

Data is given below

[TABLE="width: 337"]
<tbody>[TR]
[TD]Roller Coaster[/TD]
[TD]Amusement Park[/TD]
[TD]Type [/TD]
[/TR]
[TR]
[TD]Air [/TD]
[TD]Alton Towers[/TD]
[TD]Alloys[/TD]
[/TR]
[TR]
[TD]Boomerang [/TD]
[TD]Pleasure Island[/TD]
[TD]Steel[/TD]
[/TR]
[TR]
[TD]Cobra [/TD]
[TD]Paultons Park [/TD]
[TD]Wood[/TD]
[/TR]
[TR]
[TD]Colossus[/TD]
[TD]Thorpe Park[/TD]
[TD]Wood[/TD]
[/TR]
[TR]
[TD]Corkscrew[/TD]
[TD]Alton Towers[/TD]
[TD]Alloys[/TD]
[/TR]
[TR]
[TD]Corkscrew[/TD]
[TD]Flamingo [/TD]
[TD]Steel[/TD]
[/TR]
[TR]
[TD]Crazy Mouse[/TD]
[TD]South Pier[/TD]
[TD]Alloys[/TD]
[/TR]
[TR]
[TD]Crazy Mouse[/TD]
[TD]Brighton Pier[/TD]
[TD]Wood[/TD]
[/TR]
[TR]
[TD]Enigma[/TD]
[TD]Pleasurewood[/TD]
[TD]Steel[/TD]
[/TR]
[TR]
[TD]Express[/TD]
[TD]Scotland's Theme park [/TD]
[TD]Alloys[/TD]
[/TR]
[TR]
[TD]Fantasy Mouse[/TD]
[TD]Fantasy Island[/TD]
[TD]Alloys[/TD]
[/TR]
[TR]
[TD]G Force[/TD]
[TD]Manor Park [/TD]
[TD]Wood[/TD]
[/TR]
[TR]
[TD]Grand National [/TD]
[TD]Pleasure Beach[/TD]
[TD]Steel[/TD]
[/TR]
[TR]
[TD]Infusion[/TD]
[TD]Pleasure Beach[/TD]
[TD]Wood[/TD]
[/TR]
[TR]
[TD]IRN-BRU Revolution[/TD]
[TD]Pleasure Beach[/TD]
[TD]Wood[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

[TABLE="width: 229"]
<tbody>[TR]
[TD]Roller Coaster
[/TD]
[TD]Amusement Park
[/TD]
[/TR]
[TR]
[TD]Air
[/TD]
[TD]Alton Towers
[/TD]
[/TR]
[TR]
[TD]Corkscrew
[/TD]
[TD]Alton Towers
[/TD]
[/TR]
[TR]
[TD]Crazy Mouse
[/TD]
[TD]South Pier
[/TD]
[/TR]
[TR]
[TD]Express
[/TD]
[TD]Scotland's Theme park
[/TD]
[/TR]
[TR]
[TD]Fantasy Mouse
[/TD]
[TD]Fantasy Island
[/TD]
[/TR]
</tbody>[/TABLE]

Additional Information 1 – The code should be dynamic in nature so as to handle modified/additional/lesser data than the one currently fed in the attached file. That is, the code should still work in case further ‘Type’ of roller coasters is added or the current data is reduced to 1 or 2 ‘Type’. (The data entries will always start from row 2, but can have any number of entries)

Additional Information 2 – Create your code in such manner that the user has to only input/change the data manually and then run the code without any further manual intervention. Assume that there will be no blank entries in the data.

Notes for good Coding practice – Declare all the used variables. Add ‘Option Explicit’ at the top of the module to help find any undeclared variable. Give meaningful names to variables declared. Additionally, as discussed, Please use the indentation (‘Tab’ key on the keyboard) for the statements within ‘IF_Else’ and Loops to help with the review. Add your comments at the start of each block of code to help with the review.

Hint1 – Find the last row used to find the number of total entries. This will help you avoiding blank runs in the loops and save up you run time.
Hint2 – You can store the unique ‘Type’ in a dynamic array which can be used going forward in the code.
Hint3 – Delete all the sheets except for Data sheets when rerunning the code to avoid errors.

 

[Fazza]

Re: Help needed with VBA array exercise urgent

hello. What specific help is wanted?

 

[dkohli]

Re: Help needed with VBA array exercise urgent

This is the data
[TABLE="width: 337"]
<tbody>[TR]
[TD]Roller Coaster[/TD]
[TD]Amusement Park[/TD]
[TD]Type [/TD]
[/TR]
[TR]
[TD]Air [/TD]
[TD]Alton Towers[/TD]
[TD]Alloys[/TD]
[/TR]
[TR]
[TD]Boomerang [/TD]
[TD]Pleasure Island[/TD]
[TD]Steel[/TD]
[/TR]
[TR]
[TD]Cobra [/TD]
[TD]Paultons Park [/TD]
[TD]Wood[/TD]
[/TR]
[TR]
[TD]Colossus[/TD]
[TD]Thorpe Park[/TD]
[TD]Wood[/TD]
[/TR]
[TR]
[TD]Corkscrew[/TD]
[TD]Alton Towers[/TD]
[TD]Alloys[/TD]
[/TR]
[TR]
[TD]Corkscrew[/TD]
[TD]Flamingo [/TD]
[TD]Steel[/TD]
[/TR]
[TR]
[TD]Crazy Mouse[/TD]
[TD]South Pier[/TD]
[TD]Alloys[/TD]
[/TR]
[TR]
[TD]Crazy Mouse[/TD]
[TD]Brighton Pier[/TD]
[TD]Wood[/TD]
[/TR]
[TR]
[TD]Enigma[/TD]
[TD]Pleasurewood[/TD]
[TD]Steel[/TD]
[/TR]
[TR]
[TD]Express[/TD]
[TD]Scotland's Theme park [/TD]
[TD]Alloys[/TD]
[/TR]
[TR]
[TD]Fantasy Mouse[/TD]
[TD]Fantasy Island[/TD]
[TD]Alloys[/TD]
[/TR]
[TR]
[TD]G Force[/TD]
[TD]Manor Park [/TD]
[TD]Wood[/TD]
[/TR]
[TR]
[TD]Grand National [/TD]
[TD]Pleasure Beach[/TD]
[TD]Steel[/TD]
[/TR]
[TR]
[TD]Infusion[/TD]
[TD]Pleasure Beach[/TD]
[TD]Wood[/TD]
[/TR]
[TR]
[TD]IRN-BRU Revolution[/TD]
[TD]Pleasure Beach[/TD]
[TD]Wood[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

 

[dkohli]

Re: Help needed with VBA array exercise urgent

and this is the question:

1. Find unique ‘Types’ of Roller coasters in the given data.
2. Add new worksheets in the same workbook and name them on the type of Roller Coaster. (So based on the current data, you will have to create three sheets by the name of unique ‘Type’ - Alloys, Wood and Steel).
3. Each sheet should contain the Roller Coaster and Amusement Park name according to the type of roller coaster. For e.g. the ‘Alloys’ sheet should contain Roller Coaster – Air and Amusement Park – Alton Towers and so on, as shown in the below table -

[TABLE="width: 229"]
<tbody>[TR]
[TD]Roller Coaster
[/TD]
[TD]Amusement Park
[/TD]
[/TR]
[TR]
[TD]Air
[/TD]
[TD]Alton Towers
[/TD]
[/TR]
[TR]
[TD]Corkscrew
[/TD]
[TD]Alton Towers
[/TD]
[/TR]
[TR]
[TD]Crazy Mouse
[/TD]
[TD]South Pier
[/TD]
[/TR]
[TR]
[TD]Express
[/TD]
[TD]Scotland's Theme park
[/TD]
[/TR]
[TR]
[TD]Fantasy Mouse
[/TD]
[TD]Fantasy Island
[/TD]
[/TR]
</tbody>[/TABLE]

 

[dkohli]

Lets take this step by step
Please let me know the code to find out unique type in each column

 

[Fluff]

As this is a training exercise, the idea is that you do the work yourself.
To get a list of the various types of coaster, have a look at dictionaries.
https://excelmacromastery.com/vba-dictionary/

 

[Fazza]

or maybe collections
or use advanced filter
or a query using SELECT DISTINCT
for sure there will be many other ways

please post the code you have with description of the bit for which you want help