VBA - Concatenate UDF

[Fixed]

Hello!

I'm trying to develop the concatenate UDF, that should concatenate values in 2 columns for each row:

Function ConcatData(a As Integer, b As Integer) As Variant
For i = 1 To Cells(Rows.Count, a).End(xlUp).Row
    ConcatData = Cells(i, a).Value & " " & Cells(i, b).Value
Next i
End Function

[TABLE="width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C (UDF)
[/TD]
[TD]C (correct value)
[/TD]
[TD]C (incorrect value)<strike></strike>
[/TD]
[/TR]
[TR]
[TD]Ford
[/TD]
[TD]Escort
[/TD]
[TD]=ConcatData<strike></strike>(1,2)
[/TD]
[TD]Ford Escort
[/TD]
[TD]Ford Escort<strike></strike>
[/TD]
[/TR]
[TR]
[TD]Ford
[/TD]
[TD]Mustang
[/TD]
[TD]=ConcatData<strike></strike>(1,2)<strike></strike>
[/TD]
[TD]Ford Mustang
[/TD]
[TD]Ford Escort<strike></strike>
[/TD]
[/TR]
</tbody>[/TABLE]

In column "C (correct value)" - the values I want to get, in column "C (incorrect value)<strike></strike>" - the values I getting now.

I need your advise.
Thank you in advance.

 

[MrKowz]

Might I ask why use a UDF in place of the defined CONCATENATE() function? Is there a reason you are needing to use a column reference number instead of just referring directly to the range?

 

[MickG]

Try this:-
Place in "C1", select "A1" for the "Rng", and drag down.

Function ConcatData(rng) As String
ConcatData = rng & " " & rng.Offset(, 1).Value
End Function

 

[Fixed]

Might I ask why use a UDF in place of the defined CONCATENATE() function? Is there a reason you are needing to use a column reference number instead of just referring directly to the range?

1. I want to use it in another UDF.
2. There are too many parameters and I want just to set the column number (or character - "A" for example) in the code.

 

[Fixed]

Thanks, MickG, it works good, but I can set just number of column, or name of column ("A"), or range ("A:A").
I'm sorry I didn't tell it earlier.

 

[MrKowz]

I think we need the bigger picture to understand the context of the request. As a UDF, it is not row-aware. You could add an extra argument which you enter the row number or range reference (to get the row number from), which would fix your issue, however I'm not sure if it would be best practice.

For example:

Function ConcatData(a As Integer, b As Integer, r as integer) As Variant
ConcatData = Cells(r, a).Value & " " & Cells(r, b).Value
End Function

or

Function ConcatData(a As Integer, b As Integer, rng as Range) As Variant
ConcatData = Cells(rng.Row, a).Value & " " & Cells(rng.Row, b).Value
End Function

 

[Rick Rothstein]

As a UDF, it is not row-aware.

It can be though by using Application.Caller...

[table="width: 500"]
[tr]
	[td]Function ConcatData(FirstCol As Variant, SecondCol As Variant) As String
  ConcatData = Cells(Application.Caller.Row, FirstCol) & " " & Cells(Application.Caller.Row, SecondCol)
End Function[/td]
[/tr]
[/table]

 

[MrKowz]

It can be though by using Application.Caller...

[table="width: 500"]
[tr]
	[td]Function ConcatData(FirstCol As Variant, SecondCol As Variant) As String
  ConcatData = Cells(Application.Caller.Row, FirstCol) & " " & Cells(Application.Caller.Row, SecondCol)
End Function[/td]
[/tr]
[/table]

Google has failed me again! Thanks for the tip, Rick. I had thought they COULD be, but after a handful of Googling, I wasn't finding anything.

 

[Fixed]

Incredibly, Rick, you saved me again!
That works as I need!

 

[Rick Rothstein]

Google has failed me again! Thanks for the tip, Rick. I had thought they COULD be, but after a handful of Googling, I wasn't finding anything.

You are welcome for the tip. As for Google... maybe 15 or so years ago, I could find anything I searched for, but somewhere along the line Google changed it search engine and I can barely find what I look for even when I know it is there. Now Google insists on showing me everything including the ******* sink even when those items are just barely related to what I was looking for.