Wrox Programmer Forums
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 3rd, 2008, 10:09 PM
Registered User
 
Join Date: Dec 2008
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Combination of data

Hi i want to develop test data sheet using macros in the first sheet i have data like this

column1 column2 column3 ........ column8
 A Blue India 1
 B yellow Australia 2
 C Red WI 3
         Green
         Red

In the first i have a button when i click on the button the in second sheet data should appear with all possible combinations like this 3*5*3*3 =135 combinations

Column1 Column2 Column3 ........ Column8
 A Blue India 1
 A Blue India 2
 . ... ... 3
 A Blue India
 A

 Can any body help me doing this

Thanks in advance..

msuneel
 
Old January 2nd, 2009, 11:06 PM
Registered User
 
Join Date: Jan 2009
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Generating Combinations - using an Excel VBA Macro

Suneel,

You can break down your task into several stages as follows :

1. Create three arrays, one to hold the list of numbers , a second to hold the list of alphabetical letters, a third array to store the list of colours and a fourth array to store the list of countries.

2. Get the values from sheet 1 and place/ save them to their respective arrays.

3. Step through the arrays using nested loops to generate all possible combinations.

The code will look something like this:

Code:
 
 

for i=0 to UBound (ArrayLetters)
for j=0 to UBound (ArrayColours)
for k=1 to UBound (ArrayCountries)
for L= 1 to UBound(ArrayNumbers)
'create combination and save to an array of combinations
ArrayCombinations(m) = ArrayLetters(i) & " " & ArrayColours(j) & " " & ArrayCountries(k) & " " & ArrayNumbers(L)
'increment m by one to hold array index for next combination
m=m+1
Next
Next
Next
Next
I hope this sets you back on track to solving your problem.

Regards,

Jake

Courtesy of

http://www.Excel-Expert.co.uk
http://www.MacroExpert.co.uk
http://www.Officesoftworks.co.uk





Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem in Checkbox Combination & sorting method.. akshaymehta007 Classic ASP Professional 0 December 28th, 2007 01:32 AM
Graph combination Ulli Hoeness BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 0 October 26th, 2005 05:05 AM
Combination of ASP and PHP login system Dimitris Classic ASP Professional 0 June 29th, 2005 05:17 AM
how to post a key combination successfully? Greywacke VB How-To 0 October 14th, 2003 01:09 PM
Chap 4--using a combination of operators mixtli BOOK: Beginning PHP4/PHP 5 ISBN: 978-0-7645-4364-7; v5 ISBN: 978-0-7645-5783-5 2 June 9th, 2003 04:55 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.