Assign =concat( to a button?

[donnabee]

Hi there!
I'm essentially doing assessments in a workbook that has lines cut and pasted from a .csv
The csv is a list of photo names, coordinates etc.
I concat the photo names and then later all that information is fed into another workbook for upload - and the concat function tells that upload what photos to "take" from the folder.

The thing is, these sheets can be 10,000 lines plus so when identifying what photos I need, I end up typing =concat( hundreds of times.

I'd like to know if I can just have a button which would write the concat equation for me and I just go ahead and select the lines from there? So I select the cell where I'll be using the formula, press the button then hold control and click the cells containing the photos I need.

Please let me know if I haven't explained everything well enough.

 

[RoryA]

You could also do something like this:

activecell.formula = "=CONCAT(A1)"
application.Dialogs(xlDialogFunctionWizard).Show

then just replace the A1 reference with the cells you want in the dialog.

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Add a macro to insert the concat formula into a cell?

Hi there! I'm basically assessing photos and what you can see in the attachment is the traffic light system. Green - start Yellow - damage Red - stop Every green cell in the MARS PHOTOS column, I type =concat( then I hold control and choose all the colored lines, hit enter, keep...

www.excelforum.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[donnabee]

See if you could use this. I have placed a Forms button in A1 (but it could be anywhere) and attached the following macro to it.

View attachment 84213

Sub ConcatNames()
  Dim rFormula As Range, rPics As Range
 
  Set rFormula = Intersect(Selection, Columns("B"))
  Set rPics = Intersect(Selection, Columns("A"))
  If rFormula Is Nothing Or rPics Is Nothing Then
    MsgBox "Error"
  Else
    rFormula.Formula = "=CONCAT(" & rPics.Address(0, 0) & ")"
  End If
End Sub

Now select all the cells in column A that you want concatenated and the cell in column B where you want the formula. It does not matter what order you do those things.
I selected all the green cells (cell B20 was empty when I selected it) and then pressed the button

donnabee.xlsm
	A	B
1	Filename
2	Pic1|
3	Pic2|
4	Pic3|
5	Pic4|
6	Pic5|
7	Pic6|
8	Pic7|
9	Pic8|
10	Pic9|
11	Pic10|
12	Pic11|
13	Pic12|
14	Pic13|
15	Pic14|
16	Pic15|
17	Pic16|
18	Pic17|
19	Pic18|
20	Pic19|	Pic2|Pic4|Pic5|Pic9|Pic11|Pic12|Pic13|Pic14|Pic18|
21	Pic20|
Sheet1

Cell Formulas
Range	Formula
B20	B20	=CONCAT(A3,A5:A6,A10,A12:A15,A19)

THANK YOU THANK YOU THANK YOU THANK YOU it works perfectly!!!

I can't thank you enough, this is amazing!!! Exactly what I needed!! Thank you!

 

[Peter_SSs]

You are very welcome. Glad it worked well for you. Thanks for the follow-up.