+ Reply to Thread
Results 1 to 7 of 7

Inserting and formating variables in header

Hybrid View

Guest Inserting and formating... 03-16-2006, 08:00 AM
Guest RE: Inserting and formating... 03-16-2006, 09:20 AM
Guest RE: Inserting and formating... 03-16-2006, 11:25 AM
Guest RE: Inserting and formating... 03-16-2006, 11:55 AM
Guest RE: Inserting and formating... 03-16-2006, 01:55 PM
Guest Re: Inserting and formating... 03-16-2006, 09:10 PM
Guest RE: Inserting and formating... 03-16-2006, 01:52 PM
  1. #1
    Strabo
    Guest

    Inserting and formating variables in header

    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.


  2. #2
    Tom Ogilvy
    Guest

    RE: Inserting and formating variables in header

    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.
    >


  3. #3
    Strabo
    Guest

    RE: Inserting and formating variables in header

    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.
    > >


  4. #4
    Tom Ogilvy
    Guest

    RE: Inserting and formating variables in header

    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.
    > > >


  5. #5
    Strabo
    Guest

    RE: Inserting and formating variables in header

    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.
    > > > >


  6. #6
    Tom Ogilvy
    Guest

    Re: Inserting and formating variables in header

    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.
    > > > > >




  7. #7
    Strabo
    Guest

    RE: Inserting and formating variables in header

    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.
    > > > >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1