VBA Concatenation help

[KHurley]

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]

 

[mole999]

concerned there appears to be coverage holes at E21 / E23 / C24 / E24 / E25 / E28 / E30 / E32 etc

 

[mole999]

might =IF($C$7="","",CONCATENATE("Including: ",INDEX($E$19:$E$47,(MATCH($C$7,$C$19:$C$47,0)))))

work for you

 

[Snakehips]

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

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.

 

[KHurley]

Good spot bro

 

[KHurley]

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]

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.