INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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?








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

Hi,

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

ID Codes 
AA 
BB 
 
...and
Category 
Red 
Yellow
Black 
White
 

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.

CODE

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
Cat	ID
Red 	AA 
Red 	BB 
Yellow	AA 
Yellow	BB 
Black 	AA 
Black 	BB 
White	AA 
White	BB 
 


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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

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

And the code:

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.

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

Here's a macro to get you started:

CODE -->

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 

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

(OP)
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










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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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.

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

(OP)
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

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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

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

You said: "your code works perfectly" Great.

Now I see your data looks like this (which is fine):
(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
 
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)

CODE

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.

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

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/getfile.aspx?folder=0...

combo

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

(OP)
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.




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

Quote:

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

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close