|
Creating a list / concatenate function
I want to create a list using the concatenate function in Excel 2007. (Or
another method if something else works better.) The list I'm creating is code that will be copied and pasted into CADD software that will automatically number plan sheets. What I can't figure out is how to properly combine the lines of code together with the list of sheets. What I need to do is combine four lines of code with each sheet in a list of plan sheets. The lines of code are as follows: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", MbeSendCommand "CHANGE TEXT ALL " What I need to do is combine these lines of code (with one blank line after the three lines) with a list of plan sheets. Combine these lines: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", MbeSendCommand "CHANGE TEXT ALL " With this list: Tile Sheet Table of Contents Shematic Plan Notes As an example: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Title Sheet" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "1" MbeSendCommand "CHANGE TEXT ALL " MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Table of Contents" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "2" MbeSendCommand "CHANGE TEXT ALL " MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Schematic" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "3" MbeSendCommand "CHANGE TEXT ALL " MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Notes" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "4" MbeSendCommand "CHANGE TEXT ALL " However, when I use the concatenate function and copy it, it skips every four lines. What I get is: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Title Sheet" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "1" MbeSendCommand "CHANGE TEXT ALL " MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Notes" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "4" MbeSendCommand "CHANGE TEXT ALL " How can I combine the lines of code with my list of plan sheets so that it doesn't skip every four lines? |
Creating a list / concatenate function
Post your formula here, then we might be able to advise you where you
are going wrong. Presumably you have these items: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", MbeSendCommand "CHANGE TEXT ALL " in 3 cells somewhere, and these 4 items: Title Sheet Table of Contents Schematic Plan Notes in 4 other cells? Which cells do you use? Without seeing your formula attempts, I can't see why you need to copy the formula down. Pete On Dec 15, 4:12*pm, jaroady wrote: I want to create a list using the concatenate function in Excel 2007. *(Or another method if something else works better.) *The list I'm creating is code that will be copied and pasted into CADD software that will automatically number plan sheets. * What I can't figure out is how to properly combine the lines of code together with the list of sheets. *What I need to do is combine four lines of code with each sheet in a list of plan sheets. *The lines of code are as follows: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", MbeSendCommand "CHANGE TEXT ALL " What I need to do is combine these lines of code (with one blank line after the three lines) with a list of plan sheets. Combine these lines: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", MbeSendCommand "CHANGE TEXT ALL " With this list: Tile Sheet Table of Contents Shematic Plan Notes As an example: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Title Sheet" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "1" MbeSendCommand "CHANGE TEXT ALL " MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Table of Contents" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "2" MbeSendCommand "CHANGE TEXT ALL " MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Schematic" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "3" MbeSendCommand "CHANGE TEXT ALL " MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Notes" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "4" MbeSendCommand "CHANGE TEXT ALL " However, when I use the concatenate function and copy it, it skips every four lines. *What I get is: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Title Sheet" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "1" MbeSendCommand "CHANGE TEXT ALL " MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Notes" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "4" MbeSendCommand "CHANGE TEXT ALL " How can I combine the lines of code with my list of plan sheets so that it doesn't skip every four lines? |
Creating a list / concatenate function
Pete,
It sounds like you have an understanding of what I'm trying to do. Here is how my spreadsheet is setup to help clarify my question: Cell A1: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", Cell A2: MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", Cell A3: MbeSendCommand "CHANGE TEXT ALL" Cell B1: Title Sheet Cell B2: Table of Contents Cell B3: Schematic Plan Cell B4: Notes Cell C1: 1 Cell C2: 2 Cell C3: 3 Cell C4: 4 Cell D1: =Concatenate($A$1,B1) Cell D2: =Concatenate($A$2,C1) Cell D3: =A3 Cell D4: (Blank) Now, what I want to do is repeat this code in cells D1-D4 for pages 2, 3, 4, etc. What I do is select all four cells and use autofill to repeat the formula. However, it does not repeat the formula for sheets 2, 3, and 4. Rather, it skips every four lines. So I end up with the following: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Title Sheet" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "1" MbeSendCommand "CHANGE TEXT ALL " MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Notes" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "4" MbeSendCommand "CHANGE TEXT ALL " The list of sheets is much longer than what I have shown. This is just a small sample of what I'm trying to do. Please let me know if you need additional information. Thanks for your help. Joe "Pete_UK" wrote: Post your formula here, then we might be able to advise you where you are going wrong. Presumably you have these items: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", MbeSendCommand "CHANGE TEXT ALL " in 3 cells somewhere, and these 4 items: Title Sheet Table of Contents Schematic Plan Notes in 4 other cells? Which cells do you use? Without seeing your formula attempts, I can't see why you need to copy the formula down. Pete On Dec 15, 4:12 pm, jaroady wrote: I want to create a list using the concatenate function in Excel 2007. (Or another method if something else works better.) The list I'm creating is code that will be copied and pasted into CADD software that will automatically number plan sheets. What I can't figure out is how to properly combine the lines of code together with the list of sheets. What I need to do is combine four lines of code with each sheet in a list of plan sheets. The lines of code are as follows: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", MbeSendCommand "CHANGE TEXT ALL " What I need to do is combine these lines of code (with one blank line after the three lines) with a list of plan sheets. Combine these lines: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", MbeSendCommand "CHANGE TEXT ALL " With this list: Tile Sheet Table of Contents Shematic Plan Notes As an example: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Title Sheet" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "1" MbeSendCommand "CHANGE TEXT ALL " MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Table of Contents" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "2" MbeSendCommand "CHANGE TEXT ALL " MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Schematic" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "3" MbeSendCommand "CHANGE TEXT ALL " MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Notes" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "4" MbeSendCommand "CHANGE TEXT ALL " However, when I use the concatenate function and copy it, it skips every four lines. What I get is: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Title Sheet" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "1" MbeSendCommand "CHANGE TEXT ALL " MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Notes" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "4" MbeSendCommand "CHANGE TEXT ALL " How can I combine the lines of code with my list of plan sheets so that it doesn't skip every four lines? |
Creating a list / concatenate function
Joe,
you don't need to use the CONCATENATE function - the & operator means the same and is quicker to type. Also, I'm not sure how you are getting the quotes around Title sheet (unless you actually have "Title sheet" - with quotes - in B1) Also, if you only have sequential numbers 1 to 4 in C1:C4, then you don't really need these (but see later). Put these formulae in the cells stated: D1: =$A$1&CHAR(34)&INDIRECT("B"&INT((ROW(A1)+3)/4))&CHAR(34) D2: =$A$2&CHAR(34)&INT((ROW(A1)+3)/4)&CHAR(34) D3: =$A$3 D4: <leave blank Then you can copy D1:D4 into D5:D8, D9:D12 etc for as many times as you require. CHAR(34) gives you the quotes, so if you do have quotes around the entries in B1:B4 then you can dispense with these. Note, if you have other things in C1:C4 apart from sequential numbers, then you will have to use this in D2: =$A$2&CHAR(34)&INDIRECT("C"&INT((ROW(A1)+3)/4))&CHAR(34) Hope this helps. Pete On Dec 15, 6:08*pm, Joe wrote: Pete, It sounds like you have an understanding of what I'm trying to do. *Here is how my spreadsheet is setup to help clarify my question: Cell A1: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", Cell A2: MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", Cell A3: MbeSendCommand "CHANGE TEXT ALL" Cell B1: Title Sheet Cell B2: Table of Contents Cell B3: Schematic Plan Cell B4: Notes Cell C1: 1 Cell C2: 2 Cell C3: 3 Cell C4: 4 Cell D1: =Concatenate($A$1,B1) Cell D2: =Concatenate($A$2,C1) Cell D3: =A3 Cell D4: (Blank) Now, what I want to do is repeat this code in cells D1-D4 for pages 2, 3, 4, etc. *What I do is select all four cells and use autofill to repeat the formula. *However, it does not repeat the formula for sheets 2, 3, and 4. * Rather, it skips every four lines. *So I end up with the following: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Title Sheet" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "1" MbeSendCommand "CHANGE TEXT ALL " MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Notes" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "4" MbeSendCommand "CHANGE TEXT ALL " The list of sheets is much longer than what I have shown. *This is just a small sample of what I'm trying to do. *Please let me know if you need additional information. *Thanks for your help. Joe "Pete_UK" wrote: Post your formula here, then we might be able to advise you where you are going wrong. Presumably you have these items: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", MbeSendCommand "CHANGE TEXT ALL " in 3 cells somewhere, and these 4 items: Title Sheet Table of Contents Schematic Plan Notes in 4 other cells? Which cells do you use? Without seeing your formula attempts, I can't see why you need to copy the formula down. Pete On Dec 15, 4:12 pm, jaroady wrote: I want to create a list using the concatenate function in Excel 2007. *(Or another method if something else works better.) *The list I'm creating is code that will be copied and pasted into CADD software that will automatically number plan sheets. * What I can't figure out is how to properly combine the lines of code together with the list of sheets. *What I need to do is combine four lines of code with each sheet in a list of plan sheets. *The lines of code are as follows: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", MbeSendCommand "CHANGE TEXT ALL " What I need to do is combine these lines of code (with one blank line after the three lines) with a list of plan sheets. Combine these lines: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", MbeSendCommand "CHANGE TEXT ALL " With this list: Tile Sheet Table of Contents Shematic Plan Notes As an example: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Title Sheet" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "1" MbeSendCommand "CHANGE TEXT ALL " MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Table of Contents" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "2" MbeSendCommand "CHANGE TEXT ALL " MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Schematic" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "3" MbeSendCommand "CHANGE TEXT ALL " MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Notes" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "4" MbeSendCommand "CHANGE TEXT ALL " However, when I use the concatenate function and copy it, it skips every four lines. *What I get is: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Title Sheet" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "1" MbeSendCommand "CHANGE TEXT ALL " MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Notes" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "4" MbeSendCommand "CHANGE TEXT ALL " How can I combine the lines of code with my list of plan sheets so that it doesn't skip every four lines?- Hide quoted text - - Show quoted text - |
Creating a list / concatenate function
Pete,
That is it! Thanks for you help. I learned a few new things in the process. Joe "Pete_UK" wrote: Joe, you don't need to use the CONCATENATE function - the & operator means the same and is quicker to type. Also, I'm not sure how you are getting the quotes around Title sheet (unless you actually have "Title sheet" - with quotes - in B1) Also, if you only have sequential numbers 1 to 4 in C1:C4, then you don't really need these (but see later). Put these formulae in the cells stated: D1: =$A$1&CHAR(34)&INDIRECT("B"&INT((ROW(A1)+3)/4))&CHAR(34) D2: =$A$2&CHAR(34)&INT((ROW(A1)+3)/4)&CHAR(34) D3: =$A$3 D4: <leave blank Then you can copy D1:D4 into D5:D8, D9:D12 etc for as many times as you require. CHAR(34) gives you the quotes, so if you do have quotes around the entries in B1:B4 then you can dispense with these. Note, if you have other things in C1:C4 apart from sequential numbers, then you will have to use this in D2: =$A$2&CHAR(34)&INDIRECT("C"&INT((ROW(A1)+3)/4))&CHAR(34) Hope this helps. Pete On Dec 15, 6:08 pm, Joe wrote: Pete, It sounds like you have an understanding of what I'm trying to do. Here is how my spreadsheet is setup to help clarify my question: Cell A1: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", Cell A2: MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", Cell A3: MbeSendCommand "CHANGE TEXT ALL" Cell B1: Title Sheet Cell B2: Table of Contents Cell B3: Schematic Plan Cell B4: Notes Cell C1: 1 Cell C2: 2 Cell C3: 3 Cell C4: 4 Cell D1: =Concatenate($A$1,B1) Cell D2: =Concatenate($A$2,C1) Cell D3: =A3 Cell D4: (Blank) Now, what I want to do is repeat this code in cells D1-D4 for pages 2, 3, 4, etc. What I do is select all four cells and use autofill to repeat the formula. However, it does not repeat the formula for sheets 2, 3, and 4. Rather, it skips every four lines. So I end up with the following: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Title Sheet" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "1" MbeSendCommand "CHANGE TEXT ALL " MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Notes" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "4" MbeSendCommand "CHANGE TEXT ALL " The list of sheets is much longer than what I have shown. This is just a small sample of what I'm trying to do. Please let me know if you need additional information. Thanks for your help. Joe "Pete_UK" wrote: Post your formula here, then we might be able to advise you where you are going wrong. Presumably you have these items: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", MbeSendCommand "CHANGE TEXT ALL " in 3 cells somewhere, and these 4 items: Title Sheet Table of Contents Schematic Plan Notes in 4 other cells? Which cells do you use? Without seeing your formula attempts, I can't see why you need to copy the formula down. Pete On Dec 15, 4:12 pm, jaroady wrote: I want to create a list using the concatenate function in Excel 2007. (Or another method if something else works better.) The list I'm creating is code that will be copied and pasted into CADD software that will automatically number plan sheets. What I can't figure out is how to properly combine the lines of code together with the list of sheets. What I need to do is combine four lines of code with each sheet in a list of plan sheets. The lines of code are as follows: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", MbeSendCommand "CHANGE TEXT ALL " What I need to do is combine these lines of code (with one blank line after the three lines) with a list of plan sheets. Combine these lines: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", MbeSendCommand "CHANGE TEXT ALL " With this list: Tile Sheet Table of Contents Shematic Plan Notes As an example: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Title Sheet" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "1" MbeSendCommand "CHANGE TEXT ALL " MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Table of Contents" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "2" MbeSendCommand "CHANGE TEXT ALL " MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Schematic" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "3" MbeSendCommand "CHANGE TEXT ALL " MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Notes" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "4" MbeSendCommand "CHANGE TEXT ALL " However, when I use the concatenate function and copy it, it skips every four lines. What I get is: MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Title Sheet" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "1" MbeSendCommand "CHANGE TEXT ALL " MbeSetAppVariable "CHNGTXT", "chTextInfo.oldString", "Notes" MbeSetAppVariable "CHNGTXT", "chTextInfo.newString", "4" MbeSendCommand "CHANGE TEXT ALL " How can I combine the lines of code with my list of plan sheets so that it doesn't skip every four lines?- Hide quoted text - - Show quoted text - |
Creating a list / concatenate function
You're welcome, Joe - thanks for feeding back.
Pete On Dec 15, 8:15*pm, Joe wrote: Pete, That is it! *Thanks for you help. *I learned a few new things in the process. Joe |
| All times are GMT +1. The time now is 12:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com