Hi yourself Bob C,

Still not seeing it clearly, but is this any closer?

.Offset(4, 0).Range("A1").Value = """NOTE!! You have " &
Format(Range("G1"), "##") & " addresse/s to remove etc. etc."""


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Robert Christie" wrote in message
...
Hi Bob

The formula should give an answer of;

NOTE!! You have 10 addresses to remove from File......

The numeric value 10 comes from a countif formula in cell G1.

My problem is joining the 3 parts into 1 formula.
Part1 "NOTE!! You have" + part2 G1 + part3 "addresses to remove
from....."

Hence my work around of 2 formulas concatenated to produce the answer.

As I understand it, using Formula instead of FormulaR1C1 text strings must
have 2 "" either end to indicate text.

Hope this explains it a little better.

Regards Bob C.


"Bob Phillips" wrote:

I am probably missing the point, but


.Offset(3, 0).Formula = "=""NOTE!! You have " & TEXT(G1,"##")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Robert Christie" wrote in message
...
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.

I'm 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