Excel macro to concatenate dynamically

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);

1) Start with "="
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.

Many Thanks in advance for your advice!

Target-to-BO-Conversion.xlsx
hendrkleAsked:
Who is Participating?
 
NorieVBA ExpertCommented:
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

Open in new window

0
 
StephenJRCommented:
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

Open in new window

0
 
TommySzalapskiCommented:
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

Open in new window

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
StephenJRCommented:
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

Open in new window

0
 
hendrkleAuthor Commented:
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
 
TommySzalapskiCommented:
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
Shell ("notepad.exe " & fileName)
End If

End Sub

Open in new window

0
 
hendrkleAuthor Commented:
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
 
TommySzalapskiCommented:
Sorry. I should have seen that.
Change
Shell ("notepad.exe " & fileName)
to
Shell  "notepad.exe " & fileName, vbNormalFocus

You do need to remove the parentheses.
0
 
hendrkleAuthor Commented:
Thank you for you complete and fast response!
0
 
TommySzalapskiCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.