Solved

Excel macro to concatenate dynamically

Posted on 2010-11-11
10
922 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:hendrkle
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 24

Expert Comment

by:StephenJR
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

Open in new window

0
 
LVL 37

Expert Comment

by:TommySzalapski
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

Open in new window

0
 
LVL 24

Expert Comment

by:StephenJR
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

Open in new window

0
 
LVL 33

Accepted Solution

by:
Norie earned 500 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

Open in new window

0
 

Author Comment

by:hendrkle
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 37

Expert Comment

by:TommySzalapski
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

Shell ("notepad.exe " & fileName)

End If



End Sub

Open in new window

0
 

Author Comment

by:hendrkle
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

by:TommySzalapski
ID: 34136311
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
 

Author Closing Comment

by:hendrkle
ID: 34136824
Thank you for you complete and fast response!
0
 
LVL 37

Expert Comment

by:TommySzalapski
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

746 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now