VBA Concatenation help

KHurley

New Member
Joined
May 14, 2017
Messages
8
Hi. I wish to concatenate a list if it meets a specific requirement.

The code that works when entering into excel worksheet looks like this:

[FONT=Arial, sans-serif]=IF(C7="","",CONCATENATE("Including:",IF($C7=$C$19,$E$19&",",""),IF($C7=$C$20,$E$20&",",""),IF($C7=$C$21,$C$21&",",""),IF($C7=$C$22,$E$22&",",""),IF($C7=$C$23,$C$23&",",""),IF($C7=$C21,$C21&",",""),IF($C7=$C$25,$C$25&",",""),IF($C7=$C$26,$E$26&",",""),IF($C7=$C$27,$E$27&",",""),IF($C7=$C$28,$C$28&",",""),IF($C7=$C$29,$E$29&",",""),IF($C7=$C$30,$C$30&",",""),IF($C7=$C$31,$E$31&",",""),IF($C7=$C$32,$C$32&",",""),IF($C7=$C$33,$E$33&",",""),IF($C7=$C$34,$E$34&",",""),IF($C7=$C$35,$C$35&",",""),IF($C7=$C$36,$E$36&",",""),IF($C7=$C$37,$C$37&",",""),IF($C7=$C$38,$E$38&",",""),IF($C7=$C$39,$E$39&",",""),IF($C7=$C$40,$C$40&",",""),IF($C7=$C$41,$E$41&",",""),IF($C7=$C$42,$C$42&",",""),IF($C7=$C$43,$E$43&",",""),IF($C7=$C$44,$C$44&",",""),IF($C7=$C$45,$E$45&",",""),IF($C7=$C$46,$C$46&",",""),IF($C7=$C$47,$E$47&",","")))[/FONT]

[FONT=Arial, sans-serif]VBA really does not like when i try to insert this code in to a cell using the .Formula ("function"?) function.
[/FONT]
MY QUESTION, therefore is how would i enter this into VBA to get the result that i see in the front end of excel when i use the above code?

[FONT=Arial, sans-serif]To provide some context. I have a category of control, which has numerous sub controls. I am noting the description of the category control as the concatenation of the sub controls.

The form is for front users to populate therefore i have used this code to make it as fail proof as possible.

Thank you
Kevin
[/FONT]
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
concerned there appears to be coverage holes at E21 / E23 / C24 / E24 / E25 / E28 / E30 / E32 etc
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
might =IF($C$7="","",CONCATENATE("Including: ",INDEX($E$19:$E$47,(MATCH($C$7,$C$19:$C$47,0)))))

work for you
 
Last edited:

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,187
Office Version
  1. 2013
Platform
  1. Windows
Kevin, I have not considered whether or not your formula can rationalised as per say mole99's.

Either way, when assigning the formula using vba you need to deal with the fact that your native formula contains quote marks, lots of them. Vba then sees that as a series / muddle of strings that it cannot make sense of. If you google you will see that you need to double up on internal quotes or concatenate a string using the Chr(34) code for a quote mark.

So assigning your original formula you would I think need to use e.g. .....

Rich (BB code):
Range("A7").Formula = "=IF(C7="""","""",CONCATENATE(""Including:"",IF($C7=$C$19,$E$19&"","",""""),IF($C7=$C$20,$E$20&"","",""""),IF($C7=$C$21,$C$21&"","",""""),IF($C7=$C$22,$E$22&"","",""""),IF($C7=$C$23,$C$23&"","",""""),IF($C7=$C21,$C21&"","",""""),IF($C7=$C$25,$C$25&"","",""""),IF($C7=$C$26,$E$26&"","",""""),IF($C7=$C$27,$E$27&"","",""""),IF($C7=$C$28,$C$28&"","",""""),IF($C7=$C$29,$E$29&"","",""""),IF($C7=$C$30,$C$30&"","",""""),IF($C7=$C$31,$E$31&"","",""""),IF($C7=$C$32,$C$32&"","",""""),IF($C7=$C$33,$E$33&"","",""""),IF($C7=$C$34,$E$34&"","",""""),IF($C7=$C$35,$C$35&"","",""""),IF($C7=$C$36,$E$36&"","",""""),IF($C7=$C$37,$C$37&"","",""""),IF($C7=$C$38,$E$38&"","",""""),IF($C7=$C$39,$E$39&"","",""""),IF($C7=$C$40,$C$40&"","",""""),IF($C7=$C$41,$E$41&"","",""""),IF($C7=$C$42,$C$42&"","",""""),IF($C7=$C$43,$E$43&"","",""""),IF($C7=$C$44,$C$44&"","",""""),IF($C7=$C$45,$E$45&"","",""""),IF($C7=$C$46,$C$46&"","",""""),IF($C7=$C$47,$E$47&"","","""")))"

Hope that helps.
 
Last edited:

KHurley

New Member
Joined
May 14, 2017
Messages
8

ADVERTISEMENT

Good spot bro
 

KHurley

New Member
Joined
May 14, 2017
Messages
8
Hey man, thanks for that, yeah i gave it a whirl thinking it would just return the first value, which it did. But then it later occurred to me i could just not anchor the index area and as autofill down occurs it would return the next, would have been far less heavy on the typing and that error you picked up wouldn't have occurred. My bad habits led me to the longest if function in the world, seem to have made it work with the code provided by big tony though. Thanks
 

KHurley

New Member
Joined
May 14, 2017
Messages
8
Cheers Tony, worked a charm. It was entered into a merged cell which turned out to be a little something when i wanted to autofill down to row 16; but i just 'ignorance coded' around it and entered the code 9 times after find and replacing in word the reference point and the subsequent lines. Took a little while and the code is ugly but it did the job until i learn more. Thanks for your help lads.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,433
Messages
5,642,085
Members
417,256
Latest member
JessAw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top