Inserting and formating variables in header

[Strabo]

In cells A2006 thru B2010 of 15 sheets in my workbook I have the following
data (example):
Delivery Date: 01/29/1998
Period Beginning: 01/29/1998
Evaluation Date 1: 01/29/1999
Bond Yield: 3.3858395%
Internal Rate of Return: 5.8845034%

In each of the 15 sheets the information in column A is fixed but column B
is variable but column B will always be a date or a number(percentage) with 7
places after the decimal (as shown above).

Looking thru this site I have figured out how to insert one cell but not 8
in one right header and have been unable to figure out how to format as shown
above.

Please help.

[Tom Ogilvy]

s = ""
With Activesheet
for i = 2006 to 2010
s = .range("A" & i).Text & " " & .range("B" & i).Text & vbNewLine
Next
.PageSetup.RightHeader = s

--
Regards,
Tom Ogilvy



"Strabo" wrote:

> In cells A2006 thru B2010 of 15 sheets in my workbook I have the following
> data (example):
> Delivery Date: 01/29/1998
> Period Beginning: 01/29/1998
> Evaluation Date 1: 01/29/1999
> Bond Yield: 3.3858395%
> Internal Rate of Return: 5.8845034%
>
> In each of the 15 sheets the information in column A is fixed but column B
> is variable but column B will always be a date or a number(percentage) with 7
> places after the decimal (as shown above).
>
> Looking thru this site I have figured out how to insert one cell but not 8
> in one right header and have been unable to figure out how to format as shown
> above.
>
> Please help.
>

[Strabo]

Thank you for your reply.

as written i kept getting a end with error when I stepped thru the code.
I took out the "With Activesheet" and added Activesheet before each of the
..range and .PageSetup statements. It cycles thru but only prints the two
items in the last cells (and thankfully with the correct format) not ALL 5
rows of cells.

the modified code I used was:

s = ""
For i = 2006 To 2010
s = ActiveSheet.Range("A" & i).Text & " " & ActiveSheet.Range("B" & i).Text
& vbNewLine
Next
ActiveSheet.PageSetup.RightHeader = s

Any ideas?
Does the problem have something to do with storing items in an array?



"Tom Ogilvy" wrote:

> s = ""
> With Activesheet
> for i = 2006 to 2010
> s = .range("A" & i).Text & " " & .range("B" & i).Text & vbNewLine
> Next
> .PageSetup.RightHeader = s
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Strabo" wrote:
>
> > In cells A2006 thru B2010 of 15 sheets in my workbook I have the following
> > data (example):
> > Delivery Date: 01/29/1998
> > Period Beginning: 01/29/1998
> > Evaluation Date 1: 01/29/1999
> > Bond Yield: 3.3858395%
> > Internal Rate of Return: 5.8845034%
> >
> > In each of the 15 sheets the information in column A is fixed but column B
> > is variable but column B will always be a date or a number(percentage) with 7
> > places after the decimal (as shown above).
> >
> > Looking thru this site I have figured out how to insert one cell but not 8
> > in one right header and have been unable to figure out how to format as shown
> > above.
> >
> > Please help.
> >

[Tom Ogilvy]

I left out an s in the concatenation. the End with was also left out. Both
have been added.

s = ""
With ActiveSheet
For i = 2006 To 2010
s = s & .Range("A" & i).Text & " " & .Range("B" & i).Text _
& vbNewLine
Next
.PageSetup.RightHeader = s
End with

Make sure your margin is big enough for all the information to print.

--
Regards,
Tom Ogilvy


"Strabo" wrote:

> Thank you for your reply.
>
> as written i kept getting a end with error when I stepped thru the code.
> I took out the "With Activesheet" and added Activesheet before each of the
> .range and .PageSetup statements. It cycles thru but only prints the two
> items in the last cells (and thankfully with the correct format) not ALL 5
> rows of cells.
>
> the modified code I used was:
>
> s = ""
> For i = 2006 To 2010
> s = ActiveSheet.Range("A" & i).Text & " " & ActiveSheet.Range("B" & i).Text
> & vbNewLine
> Next
> ActiveSheet.PageSetup.RightHeader = s
>
> Any ideas?
> Does the problem have something to do with storing items in an array?
>
>
>
> "Tom Ogilvy" wrote:
>
> > s = ""
> > With Activesheet
> > for i = 2006 to 2010
> > s = .range("A" & i).Text & " " & .range("B" & i).Text & vbNewLine
> > Next
> > .PageSetup.RightHeader = s
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "Strabo" wrote:
> >
> > > In cells A2006 thru B2010 of 15 sheets in my workbook I have the following
> > > data (example):
> > > Delivery Date: 01/29/1998
> > > Period Beginning: 01/29/1998
> > > Evaluation Date 1: 01/29/1999
> > > Bond Yield: 3.3858395%
> > > Internal Rate of Return: 5.8845034%
> > >
> > > In each of the 15 sheets the information in column A is fixed but column B
> > > is variable but column B will always be a date or a number(percentage) with 7
> > > places after the decimal (as shown above).
> > >
> > > Looking thru this site I have figured out how to insert one cell but not 8
> > > in one right header and have been unable to figure out how to format as shown
> > > above.
> > >
> > > Please help.
> > >

[Strabo]

Seems to work with the modification of only 4 sets of cells - for some reason
5 sets cause an error (probably too much data). I also had to change
"vbNewLine" to "vbCr" - otherwise I got a blank line between each set of data.

Thank you very much.

"Tom Ogilvy" wrote:

> I left out an s in the concatenation. the End with was also left out. Both
> have been added.
>
> s = ""
> With ActiveSheet
> For i = 2006 To 2010
> s = s & .Range("A" & i).Text & " " & .Range("B" & i).Text _
> & vbNewLine
> Next
> .PageSetup.RightHeader = s
> End with
>
> Make sure your margin is big enough for all the information to print.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Strabo" wrote:
>
> > Thank you for your reply.
> >
> > as written i kept getting a end with error when I stepped thru the code.
> > I took out the "With Activesheet" and added Activesheet before each of the
> > .range and .PageSetup statements. It cycles thru but only prints the two
> > items in the last cells (and thankfully with the correct format) not ALL 5
> > rows of cells.
> >
> > the modified code I used was:
> >
> > s = ""
> > For i = 2006 To 2010
> > s = ActiveSheet.Range("A" & i).Text & " " & ActiveSheet.Range("B" & i).Text
> > & vbNewLine
> > Next
> > ActiveSheet.PageSetup.RightHeader = s
> >
> > Any ideas?
> > Does the problem have something to do with storing items in an array?
> >
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > s = ""
> > > With Activesheet
> > > for i = 2006 to 2010
> > > s = .range("A" & i).Text & " " & .range("B" & i).Text & vbNewLine
> > > Next
> > > .PageSetup.RightHeader = s
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > >
> > > "Strabo" wrote:
> > >
> > > > In cells A2006 thru B2010 of 15 sheets in my workbook I have the following
> > > > data (example):
> > > > Delivery Date: 01/29/1998
> > > > Period Beginning: 01/29/1998
> > > > Evaluation Date 1: 01/29/1999
> > > > Bond Yield: 3.3858395%
> > > > Internal Rate of Return: 5.8845034%
> > > >
> > > > In each of the 15 sheets the information in column A is fixed but column B
> > > > is variable but column B will always be a date or a number(percentage) with 7
> > > > places after the decimal (as shown above).
> > > >
> > > > Looking thru this site I have figured out how to insert one cell but not 8
> > > > in one right header and have been unable to figure out how to format as shown
> > > > above.
> > > >
> > > > Please help.
> > > >

[Strabo]

Seems to work with the modification of only 4 sets of cells - for some reason
5 sets cause an error (probably too much data). I also had to change
"vbNewLine" to "vbCr" - otherwise I got a blank line between each set of data.

Thank you very much.

"Tom Ogilvy" wrote:

> I left out an s in the concatenation. the End with was also left out. Both
> have been added.
>
> s = ""
> With ActiveSheet
> For i = 2006 To 2010
> s = s & .Range("A" & i).Text & " " & .Range("B" & i).Text _
> & vbNewLine
> Next
> .PageSetup.RightHeader = s
> End with
>
> Make sure your margin is big enough for all the information to print.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Strabo" wrote:
>
> > Thank you for your reply.
> >
> > as written i kept getting a end with error when I stepped thru the code.
> > I took out the "With Activesheet" and added Activesheet before each of the
> > .range and .PageSetup statements. It cycles thru but only prints the two
> > items in the last cells (and thankfully with the correct format) not ALL 5
> > rows of cells.
> >
> > the modified code I used was:
> >
> > s = ""
> > For i = 2006 To 2010
> > s = ActiveSheet.Range("A" & i).Text & " " & ActiveSheet.Range("B" & i).Text
> > & vbNewLine
> > Next
> > ActiveSheet.PageSetup.RightHeader = s
> >
> > Any ideas?
> > Does the problem have something to do with storing items in an array?
> >
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > s = ""
> > > With Activesheet
> > > for i = 2006 to 2010
> > > s = .range("A" & i).Text & " " & .range("B" & i).Text & vbNewLine
> > > Next
> > > .PageSetup.RightHeader = s
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > >
> > > "Strabo" wrote:
> > >
> > > > In cells A2006 thru B2010 of 15 sheets in my workbook I have the following
> > > > data (example):
> > > > Delivery Date: 01/29/1998
> > > > Period Beginning: 01/29/1998
> > > > Evaluation Date 1: 01/29/1999
> > > > Bond Yield: 3.3858395%
> > > > Internal Rate of Return: 5.8845034%
> > > >
> > > > In each of the 15 sheets the information in column A is fixed but column B
> > > > is variable but column B will always be a date or a number(percentage) with 7
> > > > places after the decimal (as shown above).
> > > >
> > > > Looking thru this site I have figured out how to insert one cell but not 8
> > > > in one right header and have been unable to figure out how to format as shown
> > > > above.
> > > >
> > > > Please help.
> > > >

[Tom Ogilvy]

I think there is a limit on the number of characters, but I don't know what
it is. That is interesting about the VBNewline which works most other
places. I can't say I do much multiline formatting of the headers or
footers.

--
Regards,
Tom Ogilvy



"Strabo" <Strabo@discussions.microsoft.com> wrote in message
news:B6FCE721-8D27-49F7-8359-63723FBEECFB@microsoft.com...
> Seems to work with the modification of only 4 sets of cells - for some
reason
> 5 sets cause an error (probably too much data). I also had to change
> "vbNewLine" to "vbCr" - otherwise I got a blank line between each set of
data.
>
> Thank you very much.
>
> "Tom Ogilvy" wrote:
>
> > I left out an s in the concatenation. the End with was also left out.
Both
> > have been added.
> >
> > s = ""
> > With ActiveSheet
> > For i = 2006 To 2010
> > s = s & .Range("A" & i).Text & " " & .Range("B" & i).Text _
> > & vbNewLine
> > Next
> > .PageSetup.RightHeader = s
> > End with
> >
> > Make sure your margin is big enough for all the information to print.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Strabo" wrote:
> >
> > > Thank you for your reply.
> > >
> > > as written i kept getting a end with error when I stepped thru the
code.
> > > I took out the "With Activesheet" and added Activesheet before each of
the
> > > .range and .PageSetup statements. It cycles thru but only prints the
two
> > > items in the last cells (and thankfully with the correct format) not
ALL 5
> > > rows of cells.
> > >
> > > the modified code I used was:
> > >
> > > s = ""
> > > For i = 2006 To 2010
> > > s = ActiveSheet.Range("A" & i).Text & " " & ActiveSheet.Range("B" &
i).Text
> > > & vbNewLine
> > > Next
> > > ActiveSheet.PageSetup.RightHeader = s
> > >
> > > Any ideas?
> > > Does the problem have something to do with storing items in an array?
> > >
> > >
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > s = ""
> > > > With Activesheet
> > > > for i = 2006 to 2010
> > > > s = .range("A" & i).Text & " " & .range("B" & i).Text & vbNewLine
> > > > Next
> > > > .PageSetup.RightHeader = s
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > >
> > > > "Strabo" wrote:
> > > >
> > > > > In cells A2006 thru B2010 of 15 sheets in my workbook I have the
following
> > > > > data (example):
> > > > > Delivery Date: 01/29/1998
> > > > > Period Beginning: 01/29/1998
> > > > > Evaluation Date 1: 01/29/1999
> > > > > Bond Yield: 3.3858395%
> > > > > Internal Rate of Return: 5.8845034%
> > > > >
> > > > > In each of the 15 sheets the information in column A is fixed but
column B
> > > > > is variable but column B will always be a date or a
number(percentage) with 7
> > > > > places after the decimal (as shown above).
> > > > >
> > > > > Looking thru this site I have figured out how to insert one cell
but not 8
> > > > > in one right header and have been unable to figure out how to
format as shown
> > > > > above.
> > > > >
> > > > > Please help.
> > > > >