How to Copy a range X times, and then copy/paste a variable Y times, and index?

[bsurfn99]

I have been stumped by a problem, that I have created a mess of VB examples that nothing works and nothing is coming to mind, so I'm trying a clean slate to hopefully get some fresh perspective on my problem.

Visually here is what I want to happen in Excel. I have 2 variable length tables that I am attempting to combine into unique id numbers; through a VBA script.

Example.

Inputs:
Table 1 - ID Codes
AA
BB

Table 2 - Category
Red
Yellow
Black
White


Desired Results:
Copy Table 2 in it's entirety the # of Variables in Table 1, and then copy/paste Table 1 variables the # of times found in Table 2.

So it would look like. Currently I'm anticipating that Table 2 and Table 1 variables would be in separate columns.
Red-AA
Yellow-AA
Black-AA
White-AA
Red-BB
Yellow-BB
Black-BB
White-BB

Next time I run this, I may have 10 variables in Table 1, so creating a loop that counts the # of variables seems ideal.

Please forgive my newbie question. Any guidance / ideas?

 

[SkipVought]

Hi,

Two tables: Make them Structured Tables via Insert > Tables > Table, one named tID and the other tCAT.

[pre]
ID Codes 
AA 
BB 
[/pre]
...and
[pre]
Category 
Red 
Yellow
Black 
White
[/pre]

You never specified where the tables should be, so I'm assuming they're all on the same sheet, So I put tID in coumn D and tCAT in column F. The target table will be in columns A & B.

Sub MakeTable()
    Dim rCAT As Range, rID As Range
    Dim lRow As Long
    
    lRow = 2
    For Each rCAT In [tCAT[Category]]
        For Each rID In [tID[ID Codes]]
            Cells(lRow, "A").Value = rCAT.Value
            Cells(lRow, "B").Value = rID.Value
            lRow = lRow + 1
        Next
    Next

End Sub

Result
[pre]
Cat ID
Red  AA 
Red  BB 
Yellow AA 
Yellow BB 
Black  AA 
Black  BB 
White AA 
White BB 
[/pre]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

Here is a 'less elegant' way (I like Skip's better ):

Data in Excel:
[pre]
1 A B
2 ID Codes Category
3 AA Red
4 BB Yellow
5 Black
6 White
[/pre]

And the code:

Option Explicit

Sub MakeTable()

Dim intA As Integer
Dim intB As Integer
Dim intC As Integer
Dim strA As String
Dim strB As String

intA = 2
intC = 2

Do While Range("A" & intA).Value <> ""
    strA = Range("A" & intA).Value
    intB = 2
    Do While Range("B" & intB).Value <> ""
        strB = Range("B" & intB).Value
        
        Range("D" & intC).Value = strA
        Range("C" & intC).Value = strB
        
        intB = intB + 1
        intC = intC + 1
    Loop
    intA = intA + 1
Loop

End Sub

Outcome will be in columns C and D

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[zelgar]

Here's a macro to get you started:

Sub bsurfn99()

Dim T1start As Integer, T1col As Integer, T1row As Integer
Dim T2start As Integer, T2col As Integer, T2row As Integer
Dim T3col As Integer, T3row As Integer, i As Integer, j As Integer

'  Step 1 - Determining Starting Positions of Tables

T1start = 2         '  Starting Row of Table 1
T1col = 1           '  Starting Column of Table 1
T1row = T1start

T2start = 2         '  Starting Row of Table 2
T2col = 3           '  Starting Column of Table 2
T2row = T2start

'  Step 2 - Determining the number of records in each table.  This macro assumes tables are on the same sheet.
Do Until IsEmpty(Cells(T1row, T1col))
    T1row = T1row + 1
Loop
Do Until IsEmpty(Cells(T2row, T2col))
    T2row = T2row + 1
Loop

'  Step 3 - Getting the data from each table
ReDim T1data(T1row - T1start), T2data(T2row - T2start)

T1row = T1start
Do Until IsEmpty(Cells(T1row, T1col))
    T1data(T1row - T1start) = "-" & Cells(T1row, T1col)
    T1row = T1row + 1
Loop

T2row = T2start
Do Until IsEmpty(Cells(T2row, T2col))
    T2data(T2row - T2start) = Cells(T2row, T2col)
    T2row = T2row + 1
Loop

' Step 4 - Putting combined Data into new Table 3
T3row = 10         '  Starting Row of Table 3
T3col = 1          '  Starting Column of Table 3

For i = 0 To T1row - T1start - 1
    For j = 0 To T2row - T2start - 1
        Cells(T3row, T3col) = T2data(j) & T1data(i)
        T3row = T3row + 1
    Next j
Next i
End Sub

 

[bsurfn99]

Skip, Andy, Zelgar,

All great responses. Thank you! I missed the email notifications due to a spam filter, but have since been playing around with the code and trying to make things work.

As a general note, the location is

Skip, - Your code appears to be so elegant I want to make it work, but I get a "run-time error '424: "object required." It appears to occur at the line.
For Each rCat IN [tCAT[Category]]

While I'm confident I did something incorrect, I'm also not sure what.​

Andy, - While perhaps not elegant, your code works perfectly for the newbie. (which I am) I just need to figure out how to reassign table location and sheets.

Zelgar, - While your solution was perhaps the most daunting (initial impression) I was able to make it work and in my specific table location! Thanks!

Thank you to each of you for your time and effort to help me out. I struggle with loop logic and getting your guidance is so, so apprecriated.

- BSurfN99

 

[SkipVought]

Did you make your tables, "Structured Tables via Insert > Tables > Table, one named tID and the other tCAT," With the same field names as I did in the example?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

If you tell me where (which sheet/columns) you have your 'tables' I can help you with "how to reassign table location and sheets"

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[bsurfn99]

Skip, yes that is what I attempted. Sorry, when I rename the tables it only selects the table contents and it doesn't seem to reference them.

Andy, if you are game to showing how you connect start locations. I am attempting to take info from "inputs" @C12 and then copy and transpose them to ID_TAG_GENERATOR "F11" and apply Size tags. I attached a file to help with the communication.

Thank you for your help. I have been attempting to figure it out on my own, but it's not clicking yet.

-BsurfN99

 

[SkipVought]

Okay, here's a workbook with my solution.

You apparently do not understand Excel's feature rich Structured Tables.

Select in the Structured Table, and a Table Tools > Design TAB appears in the Ribbon.

Select the Design TAB and notice the Properties Group with Table Name. This is where you change Tablen to something meaningful. My convention for table naming is tNAME; hence tID and tCAT.

As you ADD or DELETE values in a Structured Table, the table references change as well.

Structured Tables are elegant indeed!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

The code I gave you was based (as stated in my post) on the data that looks like this
(data in [blue]BLUE[/blue] is the outcome of that code)

Data in Excel:[pre]
1 A B C D
2 ID Codes Category [blue]Red AA[/blue]
3 AA Red [blue]Yellow AA[/blue]
4 BB Yellow [blue]Black AA[/blue]
5 Black [blue]White AA[/blue]
6 White [blue]Red BB[/blue]
7 [blue]Yellow BB[/blue]
8 [blue]Black BB[/blue]
9 [blue]White BB[/blue]
[/pre]

You said: "your code works perfectly" Great.

Now I see your data looks like this (which is fine):
[pre]
(Sheet Inputs)
A B C
11 Description: Abreviation
12 SUPER SMALL SS
13 SMALL SM1
14 KINDA SMALL KSM
15 KINDA MEDIUM KM
16 MEDIUM MD
17 BIGGISH BGS
18 BIGGLY BG
19 HUGE HG
[/pre]
But now you want the output to be in a separate Sheet (ID_TAG_GENERATOR) starting in cell F11 and to be concatenated in just one column. That is a different requirement. It would help from the very beginning to state what you have and where so we don't have to guess. Or attach a sample of your data like you (eventually) did.

Try this code:
(You may want to rename some variables)

Sub MakeTable()

Dim intA As Integer
Dim intB As Integer
Dim intC As Integer
Dim strA As String
Dim strB As String

intA = 12
intC = 11

Do While Sheets("Inputs").Range("C" & intA).Value <> ""
    strA = Sheets("Inputs").Range("C" & intA).Value
    intB = 12
    Do While Sheets("Inputs").Range("B" & intB).Value <> ""
        strB = Sheets("Inputs").Range("B" & intB).Value
        
        Sheets("ID_TAG_GENERATOR").Range("F" & intC).Value = strA & " - " & strB
        
        intB = intB + 1
        intC = intC + 1
    Loop
    intA = intA + 1
Loop

End Sub

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[combo]

With advanced excel features: Power Query add-in (2013) implemented as built-in get&transform feature in excel 2016 you can complete the task without any code, see attached file.
Having two input tables defined (as Skip did) I used one to get it in query, the two other queries use second table and process it in steps:
- add column and insert first table into each cell,
- expand table in each cell, so we get a product of two tables,
- return it as table in first query,
- combine columns in another one with "-" inside and delete two input columns in second query.
If you add new data to the source tables, they expand automatically. If you right-click output table and select "refresh", queries wil be resalculated.

I can't see the file attached, the link:

http://files.engineering.com/getfil...2-6129-4018-b745-22b7a80c923a&file=PQ_01.xlsx

combo

 

[bsurfn99]

Skip, Thanks for providing the file. I actually did rename the structured tables correctly. What I did NOT do correctly is rename my Table headers, to "ID Codes" and "Category" Once that was done everything worked great. Thank you for your insight and help! This is a powerful way to do things. I will have to research these structured tables more.

Andy,
Thank you for your updated code, it to appears to be working. I do apologies for keeping you guessing that what not my goal. Typically I can fumble around and redirect the logic as I need, so I was attempting to keep my question specific enough that I could "find the nugget" that would help get to a working sample.

In this case the number of "moving variables" was confusing and I do apologies for that. I don't expect you (or anyone else) to create my code, so I always feel bad asking questions. I will try to learn from this experience and post a clearer question / example in the future, but I was pretty confused when I posted my question. Anyhow, it's obvious that I'm not a world class programmer, so thank you for you insights, guidance and help!

Combo! Nice Trick! The Excel "Pool of Tricks" runs deep, that power query tool is something I'll have to look into more. It too is a working solution.

You guys Rock! Have a great weekend.

 

[SkipVought]

you want the output to be in a separate Sheet (ID_TAG_GENERATOR) starting in cell F11

Sub MakeTable()
    Dim rCAT As Range, rID As Range
    Dim lRow As Long
    Dim ws As Worksheet

    Set ws = Worksheets("ID_TAG_GENERATOR")
    
    ws.Cells(11, "F").Value = "ID-CAT"
    
    lRow = 12
    For Each rCAT In [tCAT[Category]]
        For Each rID In [tID[ID Codes]]
            ws.Cells(lRow, "F").Value = rCAT.Value & "-" & rID.Value
            lRow = lRow + 1
        Next
    Next

End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]