Solved

Excel macro to concatenate dynamically

Posted on 2010-11-11
10
958 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBA: Conversion $ vs € and vise versa 14 41
Print Wrapped Rows 3 20
Macro to Lock and Unlock a Worksheet ONLY 12 34
split data by day 21 28
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

685 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