berlinhammer
Board Regular
- Joined
- Jan 30, 2009
- Messages
- 187
Hello All,
Having my first go at writing a UDF for VBA as I'm getting frustrated by the concatenate function.
I basically want to create my own function that will concatenate values in column B, if the value in column A is correct.
The code I have tried is such a mess I shan't post it, it very much does not work. I've been looking at this link but it is a bit beyond me...
http://www.cpearson.com/excel/returningarraysfromvba.aspx
With a very simple data set as an example, I want the following output in column C:
[TABLE="width: 500"]
<TBODY>[TR]
[TD]pet
[/TD]
[TD]food
[/TD]
[TD]conc
[/TD]
[/TR]
[TR]
[TD]cat
[/TD]
[TD]chicken
[/TD]
[TD]chicken,fish,catnip
[/TD]
[/TR]
[TR]
[TD]cat
[/TD]
[TD]fish
[/TD]
[TD]chicken,fish,catnip
[/TD]
[/TR]
[TR]
[TD]cat
[/TD]
[TD]catnip
[/TD]
[TD]chicken,fish,catnip
[/TD]
[/TR]
[TR]
[TD]dog
[/TD]
[TD]bone
[/TD]
[TD]bone,chum
[/TD]
[/TR]
[TR]
[TD]dog
[/TD]
[TD]chum
[/TD]
[TD]bone,chum
[/TD]
[/TR]
</TBODY>[/TABLE]
I think I need to create a cell based function which takes a conditional range (col A) and concatenate range (col B) as inputs, stores this as a 5 by 3 array and then returns the concatenated output I wish (by referencing the stored array) based on what value is in column A. A "concatenate if", if you like. I'm new to a lot of the syntax though so if anyone has any suggestions I would be very grateful.
Many thanks in advance for any assistance
Jon
Having my first go at writing a UDF for VBA as I'm getting frustrated by the concatenate function.
I basically want to create my own function that will concatenate values in column B, if the value in column A is correct.
The code I have tried is such a mess I shan't post it, it very much does not work. I've been looking at this link but it is a bit beyond me...
http://www.cpearson.com/excel/returningarraysfromvba.aspx
With a very simple data set as an example, I want the following output in column C:
[TABLE="width: 500"]
<TBODY>[TR]
[TD]pet
[/TD]
[TD]food
[/TD]
[TD]conc
[/TD]
[/TR]
[TR]
[TD]cat
[/TD]
[TD]chicken
[/TD]
[TD]chicken,fish,catnip
[/TD]
[/TR]
[TR]
[TD]cat
[/TD]
[TD]fish
[/TD]
[TD]chicken,fish,catnip
[/TD]
[/TR]
[TR]
[TD]cat
[/TD]
[TD]catnip
[/TD]
[TD]chicken,fish,catnip
[/TD]
[/TR]
[TR]
[TD]dog
[/TD]
[TD]bone
[/TD]
[TD]bone,chum
[/TD]
[/TR]
[TR]
[TD]dog
[/TD]
[TD]chum
[/TD]
[TD]bone,chum
[/TD]
[/TR]
</TBODY>[/TABLE]
I think I need to create a cell based function which takes a conditional range (col A) and concatenate range (col B) as inputs, stores this as a 5 by 3 array and then returns the concatenated output I wish (by referencing the stored array) based on what value is in column A. A "concatenate if", if you like. I'm new to a lot of the syntax though so if anyone has any suggestions I would be very grateful.
Many thanks in advance for any assistance

Jon