Solved

Excel macro to concatenate dynamically

Posted on 2010-11-11
10
932 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

911 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

18 Experts available now in Live!

Get 1:1 Help Now