using concatenate in a macro to combine 2 cells with an ampersand between

[alabing]

column A contains the husband's name
column C contains the wife's name

If they both have the same last name (columns C and D) then column F contains "same last name"

So if they have the same last name, I want to combine the husband's and wife's first names with an ampersand between them and have that be in column E

I tired this:

Lastrow = Range("A65536").End(xlUp).Row
For x = Lastrow To 1 Step -1
If Range("f" & x).Value = "same last name" Then
Range("e" & x).Select
ActiveCell.Formula = _
"=concatenate("a" & x &" & " & "c" & x)"
End If
Next x
​

I get an "expected end of statement" compile error with the "a" flagged red.

So I tried this:

Lastrow = Range("A65536").End(xlUp).Row
For x = Lastrow To 1 Step -1
If Range("f" & x).Value = "same last name" Then
Range("e" & x).Select
ActiveCell.Formula = _
"=concatenate(a & x &" & " & c & x)"
End If
Next x
​

No error but when the macro ran, column e was blank

I tired:

Lastrow = Range("A65536").End(xlUp).Row
For x = Lastrow To 1 Step -1
If Range("f" & x).Value = "same last name" Then
Range("e" & x).Select
ActiveCell.Formula = _
"=concatenate(a & x, c & x)"
End If
Next x
​

No error but I got "0's" in column E.

Thanks,

Bill Roberts
Cumming, GA
Excel 97 ( i think )

 

[Rick Rothstein]

Do your really need the formula (if so, why make a macro insert it when you can do that so easily on the sheet yourself)? If not, don't call out to the worksheet's Concatenate function, just use VB code to create the concatentation (also, you do not need to select cells in order to change their values, you can do it directly). So, instead of this...

Range("e" & x).Select
ActiveCell.Formula = _
"=concatenate(a & x &" & " & c & x)"

try this instead...

Cells(x, "E").Value = Cells(x, "A").Value & " & " & Cells(x, "C").Value