Solved

In Excel 2007, how to write VBA code to loop through rows to create names dynamically

Posted on 2014-12-19
4
1 solution
240 Views
Last Modified: 2014-12-19
I am creating names of tables and each table is named based on a hierarchy.  The following example are made up names, but will serve the purpose of my example. As I hope you can tell, I am taking the first word from the first column and creating every possible variation from the words of following columns.  The first column (Col A) is the top hierarchy where it becomes the first part of the name, the second column (Col B) is the next highest becoming the second part of the name, and so on. What can be written via VBA to loop through each word of each column to create a new name?  The "Results" column is a sample of what I want to achieve.

Col A      Col B      Col C      Col D            Results
ABC      CAT      RED      ONE            ABC_CAT_RED_ONE
DEF      DOG      ORG      TWO            ABC_CAT_RED_TWO
GHI      PIG      YLW      THR            ABC_CAT_RED_THR
JKL      BAT      GRN      FOR            ABC_CAT_RED_FOR
MNO      BOY      BLU      FIV            ABC_CAT_RED_FIV
      MAN      PUR      SIX            ABC_CAT_RED_SIX
      BUG      BLK      SEV            ABC_CAT_RED_SEV
            WHT      EGT            ABC_CAT_RED_EGT
            BRW      NIN            ABC_CAT_RED_NIN
            PNK      TEN            ABC_CAT_RED_TEN
            LAV                  ABC_CAT_ORG_ONE
            BEI                  ABC_CAT_ORG_TWO
                              ABC_CAT_ORG_THR
                              ABC_CAT_ORG_FOR
                              ABC_CAT_ORG_FIV
                              ABC_CAT_ORG_SIX
                              ABC_CAT_ORG_SEV
                              ABC_CAT_ORG_EGT
                              ABC_CAT_ORG_NIN
                              ABC_CAT_ORG_TEN
                              etc.Example-of-Hierarchy-Naming.xlsxI am creating names of tables and each table is named based on a hierarchy.  The following example are made up names, but will serve the purpose of my example. As I hope you can tell, I am taking the first word from the first column and creating every possible variation from the words of following columns.  The first column (Col A) is the top hierarchy where it becomes the first part of the name, the second column (Col B) is the next highest becoming the second part of the name, and so on. What can be written via VBA to loop through each word of each column to create a new name?  The "Results" column is a sample of what I want to achieve.

Col A      Col B      Col C      Col D            Results
ABC      CAT      RED      ONE            ABC_CAT_RED_ONE
DEF      DOG      ORG      TWO            ABC_CAT_RED_TWO
GHI      PIG      YLW      THR            ABC_CAT_RED_THR
JKL      BAT      GRN      FOR            ABC_CAT_RED_FOR
MNO      BOY      BLU      FIV            ABC_CAT_RED_FIV
      MAN      PUR      SIX            ABC_CAT_RED_SIX
      BUG      BLK      SEV            ABC_CAT_RED_SEV
            WHT      EGT            ABC_CAT_RED_EGT
            BRW      NIN            ABC_CAT_RED_NIN
            PNK      TEN            ABC_CAT_RED_TEN
            LAV                  ABC_CAT_ORG_ONE
            BEI                  ABC_CAT_ORG_TWO
                              ABC_CAT_ORG_THR
                              ABC_CAT_ORG_FOR
                              ABC_CAT_ORG_FIV
                              ABC_CAT_ORG_SIX
                              ABC_CAT_ORG_SEV
                              ABC_CAT_ORG_EGT
                              ABC_CAT_ORG_NIN
                              ABC_CAT_ORG_TEN
                              etc.
0
Comment
Question by:kristibigo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Join The Community
  • 3
4 Comments
 
LVL 48

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40509653 
Sub Hierarchy()
Dim lngLastRowA As Long
Dim lngLastRowB As Long
Dim lngLastRowC As Long
Dim lngLastRowD As Long
Dim lngRowA As Long
Dim lngRowB As Long
Dim lngRowC As Long
Dim lngRowD As Long
Dim lngNextRow As Long

lngLastRowA = Range("A1048576").End(xlUp).Row
lngLastRowB = Range("B1048576").End(xlUp).Row
lngLastRowC = Range("C1048576").End(xlUp).Row
lngLastRowD = Range("D1048576").End(xlUp).Row

For lngRowA = 1 To lngLastRowA
    For lngRowB = 1 To lngLastRowB
        For lngRowC = 1 To lngLastRowC
            For lngRowD = 1 To lngLastRowD
                lngNextRow = lngNextRow + 1
                Cells(lngNextRow, 6) = Cells(lngRowA, 1) & "_" & Cells(lngRowB, 2) & "_" & Cells(lngRowC, 3) & "_" & Cells(lngRowD, 4)
            Next
        Next
    Next
Next
End Sub

Open in new window

0
 

Author Closing Comment

by:kristibigo
ID: 40509837
Beautiful! Thank you!
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40509946
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40509967
OK I see what you are talking about. To me however it seems strange to select the record by way of the person's initials. What would you do if there were a Karen Angela Babet and also a Karl Andrew Brown?

Would you be open to a different approach?
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Learn More
Question has a verified solution.

If you are experiencing a similar issue, please ask a related question
Excel - Did You Know !
Article by: Shums
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Forecasting in Excel 2016 for Windows and Mac
Article by: Bob
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
An Introductory Tour to Marketo Marketing Automation
Video by: Jacqueline
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Creating an Interactive Chart for Marketers in Excel
Video by: AnnieCushing
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Advertise Here

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Advertise Here