Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting variables within file names | Excel Worksheet Functions | |||
Inserting and Formating Variables in a Header using a macro | Excel Discussion (Misc queries) | |||
inserting variables in code???? | Excel Programming | |||
Inserting header @ page break | Excel Discussion (Misc queries) | |||
Formating the Header and Footer | Excel Discussion (Misc queries) |