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

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

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

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

G/Day Bob

Thank you for the code

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

works a treat, but I don't understand why.
How does Format(Range("G1"), "##") part work.

Could you explain?
What does the Format do.?
And the "##") ?

Thanks again

Regards
Aussie Bob C.

"Bob Phillips" wrote:

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

It's equivalent to the worksheet function:

=TEXT(G1,"##")

So back to excel and plop that formula in H1 (in a test worksheet).

Now put some values in G1 and watch what happens.

Robert Christie wrote:

G/Day Bob

Thank you for the code

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

works a treat, but I don't understand why.
How does Format(Range("G1"), "##") part work.

Could you explain?
What does the Format do.?
And the "##") ?

Thanks again

Regards
Aussie Bob C.

"Bob Phillips" wrote:

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







--

Dave Peterson