Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
concatenating puzzle
"?B?UGFwYSBKb25haA==?="
wrote in : 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
concatenating puzzle
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
concatenating puzzle
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
concatenating puzzle
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: "?B?UGFwYSBKb25haA==?=" wrote in : 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
concatenating puzzle
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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A puzzle about updating. | Excel Discussion (Misc queries) | |||
vlookup puzzle | Excel Worksheet Functions | |||
Excel Puzzle | Excel Worksheet Functions | |||
Can you help!!!!! New Puzzle | Excel Discussion (Misc queries) | |||
Overflow puzzle | Excel Programming |