I'm trying to use Excel to generate a simple script text file but can't seem
to get it right. The file follows a fixed repeating pattern like the one
shown below and I'm trying to get Excel to insert a line from a list (A1, A2,
A3 etc) in the third line of the repeating pattern.
This is the type of file I'm trying to create:

Fixed line 1
Fixed line 2
Cell Info A1
Fixed line 3
Fixed line 1
Fixed line 2
Cell Info A2
Fixed line 3
Fixed line 1
Fixed line 2
Cell Info A3
Fixed line 3

Excel doesn't seem to be able to repeat this type of pattern unless maybe
there's a setting I can change.
Hope somebody can help with this.

This macro takes data lin A1 to A10 (change rin range to suit), and creates
the data as you illustrated in column B.

Sub script()
rout = 1
For rin = 1 To 10
Cells(rout, 2) = "Fixed line 1"
rout = rout + 1
Cells(rout, 2) = "Fixed line 2"
rout = rout + 1
Cells(rout, 2) = Cells(rin, 1)
rout = rout + 1
Cells(rout, 2) = "Fixed line 3"
rout = rout + 1
Next
End Sub

--
Ian
--
"BigIan" wrote in message
...
I'm trying to use Excel to generate a simple script text file but can't
seem
to get it right. The file follows a fixed repeating pattern like the one
shown below and I'm trying to get Excel to insert a line from a list (A1,
A2,
A3 etc) in the third line of the repeating pattern.
This is the type of file I'm trying to create:

Fixed line 1
Fixed line 2
Cell Info A1
Fixed line 3
Fixed line 1
Fixed line 2
Cell Info A2
Fixed line 3
Fixed line 1
Fixed line 2
Cell Info A3
Fixed line 3

Excel doesn't seem to be able to repeat this type of pattern unless maybe
there's a setting I can change.
Hope somebody can help with this.

Hi Ian

Thanks for the reply. I'm not really up on Visual basic so I can only cut
and paste what you've written, although I did change line 3 to: For rin = A1
To A10.

When I ran the macro in a worksheet that was blank apart from a list in
column 1, nothing happened. I don't know if I've put it in the right place in
the Visual basic box, it gave me: Sub MACRONAME() and End Sub and I put it
after End Sub. I had tried it between these two lines but I got an error
saying it was expecting an End Sub.

Am I doing something wrong or missing something here?

Thanks,
Ian




End Sub

"Ian" wrote:

This macro takes data lin A1 to A10 (change rin range to suit), and creates
the data as you illustrated in column B.

Sub script()
rout = 1
For rin = 1 To 10
Cells(rout, 2) = "Fixed line 1"
rout = rout + 1
Cells(rout, 2) = "Fixed line 2"
rout = rout + 1
Cells(rout, 2) = Cells(rin, 1)
rout = rout + 1
Cells(rout, 2) = "Fixed line 3"
rout = rout + 1
Next
End Sub

--
Ian
--
"BigIan" wrote in message
...
I'm trying to use Excel to generate a simple script text file but can't
seem
to get it right. The file follows a fixed repeating pattern like the one
shown below and I'm trying to get Excel to insert a line from a list (A1,
A2,
A3 etc) in the third line of the repeating pattern.
This is the type of file I'm trying to create:

Fixed line 1
Fixed line 2
Cell Info A1
Fixed line 3
Fixed line 1
Fixed line 2
Cell Info A2
Fixed line 3
Fixed line 1
Fixed line 2
Cell Info A3
Fixed line 3

Excel doesn't seem to be able to repeat this type of pattern unless maybe
there's a setting I can change.
Hope somebody can help with this.

Sorry, a little more explanation seems to be needed.

There are 2 basic ways of entering cell addresses. Range("A1") is obvious.
Cells(1,1) perhaps less so. The syntax is Cells(row,column). In my code I
have used Cells because it is much easier to change the reference using a
variable. I've used rin for the inpit row and rout for the output row.
Changing "1 to 10 " to "A1 to A10" has thrown the code completely. All you
need to do is change the number range to suit the row numbers for your input
data. Cells(rin,1) means the cell whose reference row is controlled by the
variable rin and whose column reference is 1. Using Cells columns are
numbered 1=A, 2=B etc. I've used rout as the output row number and
incremented it after each line of output.

I assume you've entered the code by going Tools|Macro|Macros, typing in your
macro name and clicking create. This gives you your Sub and End Sub lines.
It sounds like you pasted all my code netween these lines, which is why you
got the error expecting End Sub. If you paste my code except for the first
and last lines, you should fare better.

Alternatively, right click on your sheet tab and click View Code. Paste all
my code into the window and run the macro.

Hope this makes sense.

--
Ian
--
"BigIan" wrote in message
...
Hi Ian

Thanks for the reply. I'm not really up on Visual basic so I can only cut
and paste what you've written, although I did change line 3 to: For rin =
A1
To A10.

When I ran the macro in a worksheet that was blank apart from a list in
column 1, nothing happened. I don't know if I've put it in the right place
in
the Visual basic box, it gave me: Sub MACRONAME() and End Sub and I put it
after End Sub. I had tried it between these two lines but I got an error
saying it was expecting an End Sub.

Am I doing something wrong or missing something here?

Thanks,
Ian




End Sub

"Ian" wrote:

This macro takes data lin A1 to A10 (change rin range to suit), and
creates
the data as you illustrated in column B.

Sub script()
rout = 1
For rin = 1 To 10
Cells(rout, 2) = "Fixed line 1"
rout = rout + 1
Cells(rout, 2) = "Fixed line 2"
rout = rout + 1
Cells(rout, 2) = Cells(rin, 1)
rout = rout + 1
Cells(rout, 2) = "Fixed line 3"
rout = rout + 1
Next
End Sub

--
Ian
--
"BigIan" wrote in message
...
I'm trying to use Excel to generate a simple script text file but can't
seem
to get it right. The file follows a fixed repeating pattern like the
one
shown below and I'm trying to get Excel to insert a line from a list
(A1,
A2,
A3 etc) in the third line of the repeating pattern.
This is the type of file I'm trying to create:

Fixed line 1
Fixed line 2
Cell Info A1
Fixed line 3
Fixed line 1
Fixed line 2
Cell Info A2
Fixed line 3
Fixed line 1
Fixed line 2
Cell Info A3
Fixed line 3

Excel doesn't seem to be able to repeat this type of pattern unless
maybe
there's a setting I can change.
Hope somebody can help with this.

Thanks Ian!,

I did the right click sheet tab and pasted the code and it ran just as I
wanted it to. Excellent!
I'll work on understanding it later but for now you've solved my problem,
thanks a million.

Ian


"Ian" wrote:

Sorry, a little more explanation seems to be needed.

There are 2 basic ways of entering cell addresses. Range("A1") is obvious.
Cells(1,1) perhaps less so. The syntax is Cells(row,column). In my code I
have used Cells because it is much easier to change the reference using a
variable. I've used rin for the inpit row and rout for the output row.
Changing "1 to 10 " to "A1 to A10" has thrown the code completely. All you
need to do is change the number range to suit the row numbers for your input
data. Cells(rin,1) means the cell whose reference row is controlled by the
variable rin and whose column reference is 1. Using Cells columns are
numbered 1=A, 2=B etc. I've used rout as the output row number and
incremented it after each line of output.

I assume you've entered the code by going Tools|Macro|Macros, typing in your
macro name and clicking create. This gives you your Sub and End Sub lines.
It sounds like you pasted all my code netween these lines, which is why you
got the error expecting End Sub. If you paste my code except for the first
and last lines, you should fare better.

Alternatively, right click on your sheet tab and click View Code. Paste all
my code into the window and run the macro.

Hope this makes sense.

--
Ian
--
"BigIan" wrote in message
...
Hi Ian

Thanks for the reply. I'm not really up on Visual basic so I can only cut
and paste what you've written, although I did change line 3 to: For rin =
A1
To A10.

When I ran the macro in a worksheet that was blank apart from a list in
column 1, nothing happened. I don't know if I've put it in the right place
in
the Visual basic box, it gave me: Sub MACRONAME() and End Sub and I put it
after End Sub. I had tried it between these two lines but I got an error
saying it was expecting an End Sub.

Am I doing something wrong or missing something here?

Thanks,
Ian




End Sub

"Ian" wrote:

This macro takes data lin A1 to A10 (change rin range to suit), and
creates
the data as you illustrated in column B.

Sub script()
rout = 1
For rin = 1 To 10
Cells(rout, 2) = "Fixed line 1"
rout = rout + 1
Cells(rout, 2) = "Fixed line 2"
rout = rout + 1
Cells(rout, 2) = Cells(rin, 1)
rout = rout + 1
Cells(rout, 2) = "Fixed line 3"
rout = rout + 1
Next
End Sub

--
Ian
--
"BigIan" wrote in message
...
I'm trying to use Excel to generate a simple script text file but can't
seem
to get it right. The file follows a fixed repeating pattern like the
one
shown below and I'm trying to get Excel to insert a line from a list
(A1,
A2,
A3 etc) in the third line of the repeating pattern.
This is the type of file I'm trying to create:

Fixed line 1
Fixed line 2
Cell Info A1
Fixed line 3
Fixed line 1
Fixed line 2
Cell Info A2
Fixed line 3
Fixed line 1
Fixed line 2
Cell Info A3
Fixed line 3

Excel doesn't seem to be able to repeat this type of pattern unless
maybe
there's a setting I can change.
Hope somebody can help with this.