Macro to concatenate a row of cells into a single cell multiple times

[psymson]

Hi

I have been given a task at work and am after some assistance or at least a point in the right direction if possible please.
I need to automate the creation of test scripts, which basically consists of taking the contents data from a row of cells, concatenating them together, then placing it into the middle of a string of text in another cell in another workbook, then replicating this several times.
For example the initial table will look something like this:

	A	B	C
1	Col 1	Col 2	Col 3
2	B3	C3	D3
3	B4	C4	D4
4	B5	C5	D5

<tbody>
</tbody>

Then the test script template will look something like this (in a separate Excel workbook).

Test Name	Test Description	Step	Step Description
Test 1	SQL to confirm (B3)	PR	Data Migrated ()
		1	Step 1
		2	Step 2
		3	Field: ()
Test 2	SQL to confirm ()	PR	Data Migrated ()
		1	Step 1
		2	Step 2
		3	Field: ()
Test 3	SQL to confirm ()	PR	Data Migrated ()
		1	Step 1
		2	Step 2
		3	Field: ()

<tbody>
</tbody>

And the end result will look something like this (will be a bit more involved with around 1000 or so rows / Tests) But to give you an idea).

Test Name	Test Description	Step	Step Description
Test 1	SQL to confirm (B3)	PR	Data Migrated (B3, C3, D3)
		1	Step 1
		2	Step 2
		3	Field: (B3)
Test 2	SQL to confirm (B4)	PR	Data Migrated (B4, C4, D4)
		1	Step 1
		2	Step 2
		3	Field: (B3)
Test 3	SQL to confirm (B5)	PR	Data Migrated (B5, C5, D5)
		1	Step 1
		2	Step 2
		3	Field: (B3)

<tbody>
</tbody>

I am thinking a VBA macro will be the best / only way to achieve this due to the amount of rows of data in order to save time doing it manually and can then also be used in the future.

Any ideas will be VERY much appreciated.

Thanks for taking the time to at least read this.

Andy

 

[frabulator]

I dont quite understand exactly what you are looking for as far as the format goes, but you should be able tweak the code below to your needs. Basically what I have set up here is a macro that will issue a command to loop through the cell values of the referenced (source) workbook and format the information into a the current workbook that the macro resides in.

This code is not tested, but it should work. The main things you want to research is how to reference workbooks (URL), how to loop (URL) and how to use If statements (URL)

'put this up top so it is seen by all macros    
Dim ColA As String
    Dim ColB As String
    Dim ColC As String
    Dim ColD As String
    Dim RowNumber As Integer
    Dim wbk As Workbook




Sub populate()
    
    'change this to the workbook that you are refrencing
    Set wbk = Workbooks.Open("C:\myworkbook.xls")
    
    'this will be the row to start at in your loop
    RowNumber = 1
    
    
    'this creates the header row
    ColA = "Test Name"
    ColB = "Test Description"
    ColC = "Step"
    ColD = "Step Description"
    
   
    populate 'refrences the populate macro
    
    RowNumber = RowNumber + 1
    
    'this starts a loop
    For i = 1 To 100000
    
        'this will make sure that the referenced workbook has a value. if it does then it will continue the loop, if not then it will exit
        
        If wbk.Sheets(1).Range("B" & i + 1).Value & wbk.Sheets(1).Range("C" & i + 1).Value & wbk.Sheets(1).Range("D" & i + 1).Value = "" Then
            Exit For
        End If
        
        
        ColA = "Test " & i 'this will generate the text "Test " and then the loop number, ie: "Test 1"
        ColB = "SQL to confirm (" & wbk.Sheets(1).Range("B" & i + 1).Value & ")" 'i dont really get where you are pulling these values from, but I added '+1' to the value of i because your row should be one more to compensate for the column header
        ColC = "PR"
        ColD = "Data Migrated (" & wbk.Sheets(1).Range("B" & i + 1).Value & wbk.Sheets(1).Range("C" & i + 1).Value & wbk.Sheets(1).Range("D" & i + 1).Value & ")"
        
        populate
        
        
        For ii = 1 To 3

            'now we add in the other rows that follow
            RowNumber = RowNumber + 1

            ColA = ""
            ColB = ""
            ColC = ii 'this will give you the new loop number - 1 through 3
            
            If ii = 1 Then 'if the first loop
                ColD = "Step Description"
            ElseIf ii = 2 Then ' if the second loop
                ColD = "Step Description"
            ElseIf ii = 3 Then ' if the third loop then
                ColD = "Step Description"
            End If
            
            populate
        Next
        
        
    Next
    
    
End Sub


Sub pupulate()


    ThisWorkbook.ActiveSheet.Range("A" & RowNumber).Value = ColA
    ThisWorkbook.ActiveSheet.Range("B" & RowNumber).Value = ColB
    ThisWorkbook.ActiveSheet.Range("C" & RowNumber).Value = ColC
    ThisWorkbook.ActiveSheet.Range("D" & RowNumber).Value = ColD
    
End Sub

I hope that helps
~Frab

 

[JLGWhiz]

You will need to do some editing as noted in the code. This assumes the data layout of both sheets is consistent, without hidden rows or columns and no merged cells. Also assumes code will be run from the destination workbook.

Sub fillParentheses()
Dim wb As Workbook, sh1 As Worksheet, sh2 As Worksheet, txt As String, lr As Long, i As Long, rw As Long
Set sh1 = ThisWorkbook.Sheets(1) 'edit sheet name
Set wb = Workbooks(2) 'Use actual workbook name since index may vary depending on openimg sequence
Set sh2 = wb.Sheets(1) 'edit sheet name
lr = sh2.Cells(Rows.Count, 1).End(xlUp).Row
    With sh2
        rw = 2
        For Each c In .Range("A2:A" & lr)
            txt = .Range("A" & c.Row).Value & ", " & .Range("B" & c.Row).Value & ", " & .Range("C" & c.Row).Value
            With sh1
            MsgBox c.Value
                .Range("B" & rw).Replace "()", "(" & c.Value & ")"
                .Range("D" & rw).Replace "()", "(" & txt & ")"
                .Range("D" & rw + 3).Replace "()", "(" & c.Value & ")"
            End With
            rw = rw + 4
        Next
    End With
End Sub

 

[frabulator]

Andy,

I saw your post yesterday (before the server reset) and I fixed the code. I had two typos in it. The code listed below has been tested and works.

You need to put this into your master sheet and change the Set wbk = Workbooks.Open("C:\testBook.xlsx") to point to the source excel document. Once that is changed run the populated macro.

'put this up top so it is seen by all macros
    Dim ColA As String
    Dim ColB As String
    Dim ColC As String
    Dim ColD As String
    Dim RowNumber As Integer
    Dim wbk As Workbook








Sub populated()
    
    'change this to the workbook that you are refrencing
    Set wbk = Workbooks.Open("C:\testBook.xlsx")
    
    'this will be the row to start at in your loop
    RowNumber = 1
    
    
    'this creates the header row
    ColA = "Test Name"
    ColB = "Test Description"
    ColC = "Step"
    ColD = "Step Description"
    
   
    populate 'refrences the populate macro
    
    
    
    'this starts a loop
    For i = 1 To 100000
    
    
        RowNumber = RowNumber + 1
        'this will make sure that the referenced workbook has a value. if it does then it will continue the loop, if not then it will exit
        
        If wbk.Sheets(1).Range("B" & i + 1).Value & wbk.Sheets(1).Range("C" & i + 1).Value & wbk.Sheets(1).Range("D" & i + 1).Value = "" Then
            Exit For
        End If
        
        
        ColA = "Test " & i 'this will generate the text "Test " and then the loop number, ie: "Test 1"
        ColB = "SQL to confirm (" & wbk.Sheets(1).Range("B" & i + 1).Value & ")" 'i dont really get where you are pulling these values from, but I added '+1' to the value of i because your row should be one more to compensate for the column header
        ColC = "PR"
        ColD = "Data Migrated (" & wbk.Sheets(1).Range("B" & i + 1).Value & wbk.Sheets(1).Range("C" & i + 1).Value & wbk.Sheets(1).Range("D" & i + 1).Value & ")"
        
        populate
        
        
        For ii = 1 To 3


            'now we add in the other rows that follow
            RowNumber = RowNumber + 1


            ColA = ""
            ColB = ""
            ColC = ii 'this will give you the new loop number - 1 through 3
            
            If ii = 1 Then 'if the first loop
                ColD = "Step Description"
            ElseIf ii = 2 Then ' if the second loop
                ColD = "Step Description"
            ElseIf ii = 3 Then ' if the third loop then
                ColD = "Step Description"
            End If
            
            populate
        Next
        
        
    Next
    
    
End Sub




Sub populate()




    ThisWorkbook.ActiveSheet.Range("A" & RowNumber).Value = ColA
    ThisWorkbook.ActiveSheet.Range("B" & RowNumber).Value = ColB
    ThisWorkbook.ActiveSheet.Range("C" & RowNumber).Value = ColC
    ThisWorkbook.ActiveSheet.Range("D" & RowNumber).Value = ColD
    
End Sub

Sorry about that, hope this one works better for you!
~Frab

 

[psymson]

Hi Frab

Thank you so much for that, I have taken the code and adapted it for the actual project I am working on.

One further question to ask if I may. In the actual project I am working on I currently have 14 columns of data (Columns A-N)

Here is the code so far:

'put this up top so it is seen by all macros
Dim ColA As String
    Dim ColB As String
    Dim ColC As String
    Dim ColD As String
    Dim ColE As String
    Dim ColF As String
    Dim ColG As String
    Dim ColH As String
    Dim ColI As String
    Dim ColJ As String
    Dim ColK As String
    Dim ColL As String
    Dim ColM As String
    Dim ColN As String
    Dim RowNumber As Integer
    Dim wbk As Workbook
Sub populatedMacro()
    
'change this to the workbook that you are refrencing (best to copy data from source to a new workbook for easier referencing)
Set wbk = Workbooks.Open("o:\Development Analyst\Excel\Automation Test\Data Mapping all fields.xlsx")
    
'this will be the row to start at in your loop
RowNumber = 10
    
'this creates the header row
ColA = "ALM Location"
ColB = "Test Name"
ColC = "Test Description"
ColD = "Step"
ColE = "Step Description"
ColF = "Expected Results"
ColG = "Level 1"
ColH = "Level 2"
ColI = "Level 3"
ColJ = "Level 4"
ColK = "Level 5"
ColL = "Author"
ColM = "Priority"
ColN = "Type"
populateMacro 'refrences the populate macro
             
'this starts a loop
For i = 1 To 100000
        
RowNumber = RowNumber + 1
'this will make sure that the referenced workbook has a value. if it does then it will continue the loop,
'if not then it will exit
        
If wbk.Sheets(1).Range("B" & i + 1).Value & wbk.Sheets(1).Range("C" & i + 1).Value & wbk.Sheets(1).Range("D" & i + 1).Value _
& wbk.Sheets(1).Range("E" & i + 1).Value & wbk.Sheets(1).Range("F" & i + 1).Value & wbk.Sheets(1).Range("G" & i + 1).Value = "" Then
        Exit For
            End If
        
ColA = "Release 2 - Unsecured Collections & Recoveries\01 System Test\C26 - Collections Migration\BSD&S\Host Updates"
'this will generate the text "Test " and then the loop number, ie: "Test 1"
ColB = "01.0" & i & " State to State"
'I added '+1' to the value of i because your row should be one more to compensate for the column header
ColC = "Data Migrated (" & wbk.Sheets(1).Range("B" & i + 1).Value & ", " & wbk.Sheets(1).Range("C" & i + 1).Value & ", " _
        & wbk.Sheets(1).Range("D" & i + 1).Value & ")"
ColD = "PR"
ColE = "Data Migrated (" & wbk.Sheets(1).Range("B" & i + 1).Value & ", " & wbk.Sheets(1).Range("C" & i + 1).Value & ", " _
        & wbk.Sheets(1).Range("D" & i + 1).Value & ")"
ColF = "PR"
ColG = "Release 2- Unsecured Collections & Recoveries"
ColH = "System Testing"
ColI = "C26 - Collections Migration"
ColJ = "BSD&S"
ColK = "Host Updates"
ColL = "7594475"
ColM = "3 - Medium"
ColN = "Manual"
populateMacro
               
For ii = 1 To 3
            'now we add in the other rows that follow
            RowNumber = RowNumber + 1
           'ColA = ""
            ColB = ""
            ColC = ""
            ColD = ii 'this will give you the new loop number - 1 through 3
            
            If ii = 1 Then 'if the first loop
                ColE = "Step Description 1"
            ElseIf ii = 2 Then ' if the second loop
                ColE = "Step Description 2"
            ElseIf ii = 3 Then ' if the third loop then
                ColE = "Step Description 3"
                     End If
                     
            populateMacro
            
        Next
            
    Next
    
End Sub

Sub populateMacro()
    ThisWorkbook.ActiveSheet.Range("A" & RowNumber).Value = ColA
    ThisWorkbook.ActiveSheet.Range("B" & RowNumber).Value = ColB
    ThisWorkbook.ActiveSheet.Range("C" & RowNumber).Value = ColC
    ThisWorkbook.ActiveSheet.Range("D" & RowNumber).Value = ColD
    ThisWorkbook.ActiveSheet.Range("E" & RowNumber).Value = ColE
    ThisWorkbook.ActiveSheet.Range("F" & RowNumber).Value = ColF
    ThisWorkbook.ActiveSheet.Range("G" & RowNumber).Value = ColG
    ThisWorkbook.ActiveSheet.Range("H" & RowNumber).Value = ColH
    ThisWorkbook.ActiveSheet.Range("I" & RowNumber).Value = ColI
    ThisWorkbook.ActiveSheet.Range("J" & RowNumber).Value = ColJ
    ThisWorkbook.ActiveSheet.Range("K" & RowNumber).Value = ColK
    ThisWorkbook.ActiveSheet.Range("L" & RowNumber).Value = ColL
    ThisWorkbook.ActiveSheet.Range("M" & RowNumber).Value = ColM
    ThisWorkbook.ActiveSheet.Range("N" & RowNumber).Value = ColN
End Sub

In Column F (Expected results) I want to show the expected outcome each of the steps, so am assuming I will need another loop. So I guess my question is, syntax wise, how would I go about adding a third loop (iii I suppose) to add Expected Result 1, Expected Result 2, Expected Result 3.

I hope that makes sense.

Many thanks once again.

Andy

 

[frabulator]

Hi Frab

Thank you so much for that, I have taken the code and adapted it for the actual project I am working on.

One further question to ask if I may. In the actual project I am working on I currently have 14 columns of data (Columns A-N)
...

In Column F (Expected results) I want to show the expected outcome each of the steps, so am assuming I will need another loop. So I guess my question is, syntax wise, how would I go about adding a third loop (iii I suppose) to add Expected Result 1, Expected Result 2, Expected Result 3.

I hope that makes sense.

Many thanks once again.

Andy

Andy,

If there are going to be adding three outcomes in column F just like there are three outcomes in column E then you simply need to modify the existing loop for column E; like so:

            For ii = 1 To 3
            'now we add in the other rows that follow
            RowNumber = RowNumber + 1
           'ColA = ""
            ColB = ""
            ColC = ""
            ColD = ii 'this will give you the new loop number - 1 through 3
            
            
            If ii = 1 Then 'if the first loop
                ColE = "Step Description 1"
                ColF = "Expected Result 1"
            ElseIf ii = 2 Then ' if the second loop
                ColE = "Step Description 2"
                ColF = "Expected Result 2"
            ElseIf ii = 3 Then ' if the third loop then
                ColE = "Step Description 3"
                ColF = "Expected Result 3"
            End If
                     
            populateMacro

It is always a good idea to eliminate all unnecessary loops and duplicate codes if possible. Doing so will greatly improve the functionality and speed of your application.

However if you would like to add in loops you need to keep in mind the RowNumber (where you are placing your data - in the code proved above we keep adding to it to keep up with what row we are on), the loop number you are currently on and the variables you define in the loop. 'Dim' ing something inside of a loop will actually slow down your program significantly as opposed to dimming it outside of the loop. Just a little tid-bit on that one

A good reference for looping can be found here.

 

[psymson]

Hi Frab
Great, thanks very much for the code and links, I have learnt a lot and can now finish exactly what I need to do.

Hi JLGWhiz
Thanks very much for your code as well, once I have finished what I am working on, I will have a look as it's always good to see another way of coding something.

I appreciate both of your time and input into this. Thanks again.