Hi

I'm trying to enter a formula via VBA to produce an alphanumeric result of:
€œYou have ## addresses to remove from etc. etc.€�€�, where ## is value in
Cell G1.

Im having trouble joining the 3 parts of formula together i.e. Text, Value
from G1, Text.
So my work around code so far is;

Cells(Rows.Count, "A").End(xlUp).Select
With Selection
.Offset(3, 0).Formula = "=""NOTE!! You have ""&G1"
.Offset(3, 1).Formula = "="" addresse/s to remove etc. etc."""
.Offset(4, 0).Range("A1").FormulaR1C1 = "=CONCATENATE(R[-1]C,R[-1]C[1])"
.Offset(4, 0).Range("A1").Copy
.Offset(4, 0).PasteSpecial Paste:=xlPasteValues, Operation:=€¦€¦€¦
End With
ActiveCell.Offset(-1, 0).Range("A1:B1").ClearContents

The formula cell changes each time code is run; so using FormulaR1C1
Relative Reference is not an option.

Can the Text, Value, Text be achieved with the one formula in this situation?

TIA.

--
Thank you

Regards
Aussie Bob C.
Using Windows XP Home + Office 2003 Pro