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

Hi Dave

Thanks for the post and the kick start for my brain.
The penny has just dropped. The lights are on and someones home.
My apoligies to both of you.
My mind was set on a Formula that would have to be copied/pasted in place.
I just failed to see what was in front of me, basically four lines of my
code into one line.
That is one classy line, which will be used in one or two other areas, that
is for sure.

I thank you both.

Regards

Aussie Bob C.

-----

"Dave Peterson" wrote:

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