Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Excel macro to concatenate dynamically

Posted on 2010-11-11
Medium Priority
968 Views
Dear experts, I have attached and example spreadsheet which has a number of value which are concatenated. The final purpose is to establish a new formula which will end of in a Business Objects measurement.

Column A contains a name and B a vale. The number of names can be anywhere between 2 and 500, as can the value.

I need a dynamic macro which will do the following in it's output (either as text in any cell or in a prompt window where I can copy it);

2) Concatenate all data in column C (starting at C2)
3) End with "N/A"
4) End with a number of close brackets ")" , one bracket per line populated in column A:B

So, if I only had two lines in the attached and column D would display:

If([Employee Name] = "Name1";9636;
If([Employee Name] = "Name2";5289;

The Macro would have to create this:

=If([Employee Name] = "Name1";9636;If([Employee Name] = "Name2";5289;"N/A"))

The second part of my question is less important but would be great as well to solve. As indicated the number of entries in column A an B may vary. I would be great to have a macro drag down the formula in column C as far down as A:B are populated.

Target-to-BO-Conversion.xlsx
0
Question by:hendrkle
[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
• 4
• 3
• 2
• +1

LVL 24

Expert Comment

ID: 34118371
See if this helps:
``````Sub x()

Dim r As Long, n As Long, s As String

n = Range("A" & Rows.Count).End(xlUp).Row

Range("C3:C" & n).Formula = "=CONCATENATE(""If([Employee Name] = "","""""",A3,"""""","";"",B3,"";"")"

r = 3

Do Until UCase(Cells(r, 3)) = "N/A"
s = s & Cells(r, 3)
r = r + 1
Loop

s = s & "N/A" & WorksheetFunction.Rept(")", r - 3)

Range("A" & n + 2) = "=" & s

End Sub
``````
0

LVL 37

Expert Comment

ID: 34118377
Try this. The string gets too long to put in a cell, so I dump it to a text file. (Make sure you use a valid path. i.e. if C:\temp does not exist, create it or use a different folder)

``````Private Sub CommandButton1_Click()
Dim lastRow, i, fnum As Integer
Dim str, ends, fileName As String

lastRow = Sheet1.UsedRange.Rows.Count

fileName = "C:\temp\out.txt"

fnum = FreeFile()

Open fileName For Output As fnum

Cells(3, 3).AutoFill Range(Cells(3, 3), Cells(lastRow, 3))

str = "="
ends = ""

For i = 3 To lastRow
str = str & Cells(i, 3)
ends = ends & ")"
Next

str = str & "N/A" & ends

Print #fnum, str

Close fnum

End Sub
``````
0

LVL 24

Expert Comment

ID: 34118428
Correction, the formula in C was wrong.
``````Sub x()

Dim r As Long, n As Long, s As String

n = Range("A" & Rows.Count).End(xlUp).Row

Range("C3:C" & n).Formula = "=CONCATENATE(""If([Employee Name] = "","""""""",A3,"""""""","";"",B3,"";"")"

r = 3

Do Until UCase(Cells(r, 3)) = "N/A"
s = s & Cells(r, 3)
r = r + 1
Loop

s = s & "N/A" & WorksheetFunction.Rept(")", r - 3)

Range("A" & n + 2) = "=" & s

End Sub
``````
0

LVL 34

Accepted Solution

Norie earned 2000 total points
ID: 34120329
Does this give the correct string?

In the message box it appears cut off, but I think that's a message box limitation - when I copy it to a text editor it looks fine.

=If([Employee Name] = "Name1";9636; If([Employee Name] = "Name2";5289;"N/A")))
=If([Employee Name] = "Name1";9636; If([Employee Name] = "Name2";5289;"N/A"))

``````Dim strFormula

arrTargets = Range("C3:C33")
arrTargets = Application.Transpose(arrTargets)

strFormula = "=" & Join(arrTargets) & Chr(34) &  "N/A" & Chr(34)& String(UBound(arrTargets), ")")

MsgBox strFormula
``````
0

Author Comment

ID: 34135476
Hi Tommy,

Your solution works great for me, thank you!

I was wondering if it is possible to modify the code to;

1) Prompt the user where he/she wants to save the output
2) Prompt the user after code execution, whether or not they want to open the text file (yes/no)

Thanks!
0

LVL 37

Expert Comment

ID: 34136162
That can be done easily.

I assumed you want the output opened in notepad and that notepad opens without the path.
``````Private Sub CommandButton1_Click()
Dim lastRow, i, fnum As Integer
Dim str, ends, fileName As String
Dim openText As VbMsgBoxResult

lastRow = Sheet1.UsedRange.Rows.Count

fileName = Application.GetSaveAsFilename(InitialFileName:="concat.txt", _
FileFilter:="Text Files, *.txt", _
Title:="Choose output file")

If fileName = "False" Then 'The user hit cancel
Exit Sub
End If

fnum = FreeFile()

Open fileName For Output As fnum

Cells(3, 3).AutoFill Range(Cells(3, 3), Cells(lastRow, 3))

str = "="
ends = ""

For i = 3 To lastRow
str = str & Cells(i, 3)
ends = ends & ")"
Next

str = str & "N/A" & ends

Print #fnum, str

Close fnum

openText = MsgBox("Open output file?", vbYesNo, "Done!")

If openText = vbYes Then
End If

End Sub
``````
0

Author Comment

ID: 34136236
Like a charm :)

Just one small thing, is it possible to activate the Notepad window, currently I can see that is open but the active window is still Excel
0

LVL 37

Expert Comment

ID: 34136311
Sorry. I should have seen that.
Change
to
Shell  "notepad.exe " & fileName, vbNormalFocus

You do need to remove the parentheses.
0

Author Closing Comment

ID: 34136824
Thank you for you complete and fast response!
0

LVL 37

Expert Comment

ID: 34136860
Did you accept the right solution? Please make sure to accept everything that helped as part of the solution, both for the experts and anyone who finds this later.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,â€¦
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, tâ€¦
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
###### Suggested Courses
Course of the Month4 days, 22 hours left to enroll