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
