Creating a list / concatenate function

[jaroady]

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?

 

[Pete_UK]

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

 

[Joe]

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]

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 D14 into D58, D912 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

 

[Joe]

Pete,

That is it! Thanks for you help. I learned a few new things in the process.

Joe

 

[Pete_UK]

You're welcome, Joe - thanks for feeding back.

Pete