MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Concatenate with code


Posted by Celeste on June 14, 2001 6:57 AM

On sheet X, I need to show the numeric value of cell D9 & the alpha value of cell E9, pulled from sheet y. How do I do that??????????????????


Posted by Aladin Akyurek on June 14, 2001 7:07 AM

As I understand it and you're not after VBA, I'd say:

=D9&" "&SheetY!E9 in some relevant cell in X.

Aladin

Posted by Joe Was on June 14, 2001 7:23 AM

On sheet X where you need the data;
='Sheet y'!D9 & " and " & 'Sheet y'!E9
The trick is to reference the sheet name with a ! next to the Cell address or Range, like Sheet1! or 'Sheet1'! both work. The concatenation is done by space then "&" space with no quotes around the ampersand. You must add any formatting on your own! If D9=123 and E9=Test then, =D9 & E9 gives 123Test and
=D9 & "and" & E9 gives 123andTest
=D9 & " and " & E9 gives 123 and Test. JSW

Posted by Celeste on June 14, 2001 8:13 AM

I do need it in VBA, to write just a formula was not the problem for me....Can you tell me how to in VBA

Posted by Aladin Akyurek on June 14, 2001 8:23 AM

No, but some VBA coder will... (NT)

Posted by Joe Was on June 14, 2001 8:36 AM

As VB Code:

Set MyData= Worksheets("Sheet y").Range("D9") & Worksheets("Sheet y").Range("E9")
MyData.Copy Destination:=Range(Worksheets("Sheet X").Range("A1"").Address)

You can avoid the Sheet tag by using a with statement.
With Worksheets("Sheet y")
your code...
End With

JSW

Posted by Barrie Davidson on June 14, 2001 8:40 AM

Hi Celeste, assuming your numeric value (D9) is also in sheet y, your code would be:
ActiveCell.Formula = "='sheet y'!D9&'sheet y'!E9"

Assuming your numeric value (D9) is in sheet X, your code would be:
ActiveCell.Formula = "=D9&'sheet y'!E9"

Both codes will put the formula in the active cell.

Regards,
Barrie