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 )

 

[JLGWhiz]

This would work:

If LCase(Range("f" & x).Value) = "same last name" Then
Range("E" & x) = Range("A" & x).Value & " & " & Range("C" & x).Value
End If

 

[alabing]

That works! Thanks so much!

 

[JLGWhiz]

That works! Thanks so much!

You're most welcome.

Regards, JLG