Hi,
Below is an example of what I am trying to accomplish, along with my current code at the bottom. I am trying to create a tool that will loop through multiple lists, generating each unique combination between them. The current code currently works close to expectation but there are 2 issues - A) the column "Output A & B" will show duplicates (understand I could remove them with another line of code but prefer avoiding that if possible) & B) if the column "Input C" is blank the code gets stuck in the loop. I think I might be missing adding in an IF statement somewhere, but am a little stuck.
What I am looking for is some help (suggested code, link to articles, etc) of how to get this current code to work 100% as expected? Extra credit but not necessary I'd love to figure out how to have the code also account for different orders between inputs A, B & C.
<tbody>
</tbody>
Below is an example of what I am trying to accomplish, along with my current code at the bottom. I am trying to create a tool that will loop through multiple lists, generating each unique combination between them. The current code currently works close to expectation but there are 2 issues - A) the column "Output A & B" will show duplicates (understand I could remove them with another line of code but prefer avoiding that if possible) & B) if the column "Input C" is blank the code gets stuck in the loop. I think I might be missing adding in an IF statement somewhere, but am a little stuck.
What I am looking for is some help (suggested code, link to articles, etc) of how to get this current code to work 100% as expected? Extra credit but not necessary I'd love to figure out how to have the code also account for different orders between inputs A, B & C.
Input A | Input B | Input C | Output A & B | Output A&B&C | |
new | car | red | new car | new car red | |
old | shoes | new shoes | new shoes red | ||
bike | new bike | new bike red | |||
old car | old car red | ||||
old shoes | old shoes red | ||||
old bike | old bike red |
<tbody>
</tbody>
Code:
Option Explicit
Sub New_Tool ()
Dim rng1 As Range, rng2 As Range, rng3 As RangeDim rngA As Range, rngB As Range, rngC As Range
Dim rngOut1 As Range, rngOut2 As Range
Set rng1 = Range("B5", Range("B5").End(xlDown))
Set rng2 = Range("c5", Range("c5").End(xlDown))
Set rng3 = Range("d5", Range("d5").End(xlDown))
Set rngOut1 = Range("F5")
Set rngOut2 = Range("G5")
For Each rngA In rng1.Cells
For Each rngB In rng2.Cells
For Each rngC In rng3.Cells
rngOut1 = rngA.Value & " " & rngB.Value
Set rngOut1 = rngOut1.Offset(1, 0)
rngOut2 = rngA.Value & " " & rngB.Value & " " & rngC.Value
Set rngOut2 = rngOut2.Offset(1, 0)
Next
Next
Next
End Sub