concatenating puzzle

I am trying to use the concatenate function in a cell that will ultimately be
part of a legend in an automated chart.
It is driving me crazy because I do this manually time and again without
issue but vba is not very congenial.
One problem I am having with the code is that the bb is showing up as a
number instead of the date format (this works manually).
Second, as I assemble the string, it seems to quit working as I add the part
that addresses endbase2.

With Range("x1")
.Value = begbase
.NumberFormat = "m/d/yy"
.Name = "bb"
End With
With Range("z1")
.Value = reviewbeg - 1
.NumberFormat = "M/D/YY"
.Name = "endbase2"
End With

Range("aa1").FormulaR1C1 = "=concatenate(""Baseline: "",bb,"" through
"",endbase2,""(Total "",basetypetotal,""events; Yearly avg
"",baselineannrate,"")"

Any thoughts would be much appreciated.
TIA

"=?Utf-8?B?UGFwYSBKb25haA==?=" <PapaJonah@discussions.microsoft.com>
wrote in news:DF7130EA-EA7E-4152-B0AE-32F9FC87F5D0@microsoft.com:

Have a look at this site for the first problem:
http://www.apostate.com/programming/vb-format.html
The problem is that 'bb' probably returns the Long-format of the date.
You have to change this using the 'Format'-function, thus:
,Format(bb,"dd-mm-yy"), instead of ,bb,

For the 2nd problem, I can't really tell what's wrong.

HTH,
CoRrRan

> I am trying to use the concatenate function in a cell that will
> ultimately be part of a legend in an automated chart.
> It is driving me crazy because I do this manually time and again
> without issue but vba is not very congenial.
> One problem I am having with the code is that the bb is showing up as
> a number instead of the date format (this works manually).
> Second, as I assemble the string, it seems to quit working as I add
> the part that addresses endbase2.
>
> With Range("x1")
> .Value = begbase
> .NumberFormat = "m/d/yy"
> .Name = "bb"
> End With
> With Range("z1")
> .Value = reviewbeg - 1
> .NumberFormat = "M/D/YY"
> .Name = "endbase2"
> End With
>
> Range("aa1").FormulaR1C1 = "=concatenate(""Baseline: "",bb,""
> through
> "",endbase2,""(Total "",basetypetotal,""events; Yearly avg
> "",baselineannrate,"")"
>
> Any thoughts would be much appreciated.
> TIA
>

Range("aa1").Formula = "=concatenate(""Baseline: ""," & Range("bb").Text
& _
","" through ""," & _
Range("endbase2").Text & _
", ""(Total "",""basetypetotal"",""events;
Yearly avg"",""baselineannrate,"")"

--

HTH

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


"Papa Jonah" <PapaJonah@discussions.microsoft.com> wrote in message
news:DF7130EA-EA7E-4152-B0AE-32F9FC87F5D0@microsoft.com...
> I am trying to use the concatenate function in a cell that will ultimately
be
> part of a legend in an automated chart.
> It is driving me crazy because I do this manually time and again without
> issue but vba is not very congenial.
> One problem I am having with the code is that the bb is showing up as a
> number instead of the date format (this works manually).
> Second, as I assemble the string, it seems to quit working as I add the
part
> that addresses endbase2.
>
> With Range("x1")
> .Value = begbase
> .NumberFormat = "m/d/yy"
> .Name = "bb"
> End With
> With Range("z1")
> .Value = reviewbeg - 1
> .NumberFormat = "M/D/YY"
> .Name = "endbase2"
> End With
>
> Range("aa1").FormulaR1C1 = "=concatenate(""Baseline: "",bb,"" through
> "",endbase2,""(Total "",basetypetotal,""events; Yearly avg
> "",baselineannrate,"")"
>
> Any thoughts would be much appreciated.
> TIA

Hey Bob,
Do you have any suggested reading to figure this stuff out? I appreciate
all the times you have bailed me out, but I would like to eventually learn
why your suggestions work. Although this suggestion did not work, I have no
understanding of why you chose the approach that you did. I do suspect this
is close and have played with it, but still can't get it to work.
Thanks for the suggestion.


"Bob Phillips" wrote:

> Range("aa1").Formula = "=concatenate(""Baseline: ""," & Range("bb").Text
> & _
> ","" through ""," & _
> Range("endbase2").Text & _
> ", ""(Total "",""basetypetotal"",""events;
> Yearly avg"",""baselineannrate,"")"
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Papa Jonah" <PapaJonah@discussions.microsoft.com> wrote in message
> news:DF7130EA-EA7E-4152-B0AE-32F9FC87F5D0@microsoft.com...
> > I am trying to use the concatenate function in a cell that will ultimately
> be
> > part of a legend in an automated chart.
> > It is driving me crazy because I do this manually time and again without
> > issue but vba is not very congenial.
> > One problem I am having with the code is that the bb is showing up as a
> > number instead of the date format (this works manually).
> > Second, as I assemble the string, it seems to quit working as I add the
> part
> > that addresses endbase2.
> >
> > With Range("x1")
> > .Value = begbase
> > .NumberFormat = "m/d/yy"
> > .Name = "bb"
> > End With
> > With Range("z1")
> > .Value = reviewbeg - 1
> > .NumberFormat = "M/D/YY"
> > .Name = "endbase2"
> > End With
> >
> > Range("aa1").FormulaR1C1 = "=concatenate(""Baseline: "",bb,"" through
> > "",endbase2,""(Total "",basetypetotal,""events; Yearly avg
> > "",baselineannrate,"")"
> >
> > Any thoughts would be much appreciated.
> > TIA
>
>
>

I'm not sure what you are suggesting. Inserting the "Format(bb,"dd-mm-yy")"
in place of "bb" gives me a syntax error. I formatted bb a couple a lines
above the concatenate problem.

"CoRrRan" wrote:

> "=?Utf-8?B?UGFwYSBKb25haA==?=" <PapaJonah@discussions.microsoft.com>
> wrote in news:DF7130EA-EA7E-4152-B0AE-32F9FC87F5D0@microsoft.com:
>
> Have a look at this site for the first problem:
> http://www.apostate.com/programming/vb-format.html
> The problem is that 'bb' probably returns the Long-format of the date.
> You have to change this using the 'Format'-function, thus:
> ,Format(bb,"dd-mm-yy"), instead of ,bb,
>
> For the 2nd problem, I can't really tell what's wrong.
>
> HTH,
> CoRrRan
>
> > I am trying to use the concatenate function in a cell that will
> > ultimately be part of a legend in an automated chart.
> > It is driving me crazy because I do this manually time and again
> > without issue but vba is not very congenial.
> > One problem I am having with the code is that the bb is showing up as
> > a number instead of the date format (this works manually).
> > Second, as I assemble the string, it seems to quit working as I add
> > the part that addresses endbase2.
> >
> > With Range("x1")
> > .Value = begbase
> > .NumberFormat = "m/d/yy"
> > .Name = "bb"
> > End With
> > With Range("z1")
> > .Value = reviewbeg - 1
> > .NumberFormat = "M/D/YY"
> > .Name = "endbase2"
> > End With
> >
> > Range("aa1").FormulaR1C1 = "=concatenate(""Baseline: "",bb,""
> > through
> > "",endbase2,""(Total "",basetypetotal,""events; Yearly avg
> > "",baselineannrate,"")"
> >
> > Any thoughts would be much appreciated.
> > TIA
> >
>
>

Hi Papa Jonah,

Not really, as this is really down to trying to understand the problem and
applying what I already know to trying to solve it. There are good books
around (John Walkenbach's, John Green and Stephen Bullen), but none of these
would address your specific question.

The best way is to keep trying, and posting here when you have a problem,
then learn from the replies.

To help you with your current problem, here is my thinking on what I saw.
- you were creating two named ranges bb and endbase2
- you then tried to include those named ranges in your concatenation formula
- you wanted the named ranges included, but you were using the range name so
it treated it as a variable
- I just changed it so that rather than using a variable bb it picked up the
workbook name (Range("bb"))

Trying it again, I guess it doesn't work as it doesn't show those dates as
dates. This might overcome that

Range("aa1").Formula = "=concatenate(""Baseline: "",""" & Range("bb").Text
& _
""","" through ""," & """" & _
Range("endbase2").Text & """" & _
", ""(Total "",""basetypetotal"",""events;Yearly
avg"",""baselineannrate,"")"

However, this is not dynamic, so if bb or endbase2 changes this formula
doesn't (which is what you were originally aiming at I guess), so you could
also try this

sFormula = "=concatenate(""Baseline: "",TEXT(bb,""m/d/yy"")" & _
","" through "",TEXT(endbase2,""m/d/yy"")" & _
", ""(Total "",""basetypetotal"",""events;Yearly
avg"",""baselineannrate,"")"

Range("AA1").Formula = sFormula

--

HTH

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


"Papa Jonah" <PapaJonah@discussions.microsoft.com> wrote in message
news:3A67C2FD-837D-4E04-ADF9-BED219B16DA9@microsoft.com...
> Hey Bob,
> Do you have any suggested reading to figure this stuff out? I appreciate
> all the times you have bailed me out, but I would like to eventually learn
> why your suggestions work. Although this suggestion did not work, I have
no
> understanding of why you chose the approach that you did. I do suspect
this
> is close and have played with it, but still can't get it to work.
> Thanks for the suggestion.
>
>
> "Bob Phillips" wrote:
>
> > Range("aa1").Formula = "=concatenate(""Baseline: ""," &
Range("bb").Text
> > & _
> > ","" through ""," & _
> > Range("endbase2").Text & _
> > ", ""(Total "",""basetypetotal"",""events;
> > Yearly avg"",""baselineannrate,"")"
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Papa Jonah" <PapaJonah@discussions.microsoft.com> wrote in message
> > news:DF7130EA-EA7E-4152-B0AE-32F9FC87F5D0@microsoft.com...
> > > I am trying to use the concatenate function in a cell that will
ultimately
> > be
> > > part of a legend in an automated chart.
> > > It is driving me crazy because I do this manually time and again
without
> > > issue but vba is not very congenial.
> > > One problem I am having with the code is that the bb is showing up as
a
> > > number instead of the date format (this works manually).
> > > Second, as I assemble the string, it seems to quit working as I add
the
> > part
> > > that addresses endbase2.
> > >
> > > With Range("x1")
> > > .Value = begbase
> > > .NumberFormat = "m/d/yy"
> > > .Name = "bb"
> > > End With
> > > With Range("z1")
> > > .Value = reviewbeg - 1
> > > .NumberFormat = "M/D/YY"
> > > .Name = "endbase2"
> > > End With
> > >
> > > Range("aa1").FormulaR1C1 = "=concatenate(""Baseline: "",bb,""
through
> > > "",endbase2,""(Total "",basetypetotal,""events; Yearly avg
> > > "",baselineannrate,"")"
> > >
> > > Any thoughts would be much appreciated.
> > > TIA
> >
> >
> >