Change the structure of words?

[StargateFan]

Is there a way to change this input:

twenty four Number of hours for earth to rotate around its axis.
laser Monochromatic light
proton Positively charged particle of an atom.
acceleration Force equals mass times ____________.




To this format which can then be picked up by a program?

twenty four
laser
proton
acceleration
*
Number of hours for earth to rotate around its axis.
Monochromatic light
Positively charged particle of an atom.
Force equals mass times ____________.



In essence, a script would take all the words in column A and spread
them vertically over rows instead.

Then the next row after that would have an aseterisk, as shown above.

And then all the explanations of the words/phrases in column B would
be listed vertically, also now in column A, underneath the asterisk.

I could do this manually but I have many rows to work with and it
would be quite time-consuming.

Thank you so much! D

 

[StargateFan]

Is there a way to change this input:

twenty four Number of hours for earth to rotate around its axis.
laser Monochromatic light
proton Positively charged particle of an atom.
acceleration Force equals mass times ____________.




To this format which can then be picked up by a program?

twenty four
laser
proton
acceleration
*
Number of hours for earth to rotate around its axis.
Monochromatic light
Positively charged particle of an atom.
Force equals mass times ____________.

[snip]

Oops, forgot to mention that row A is a header row. Sorry. <g>

 

[Sam Wilson]

sub test()

dim i as integer


with range("A2")
for i = 0 to 3
.offset(i,3).value = .offset(i,0).value
.offset(i+5,3).value = .offset(i,1).value
next i
.offset(4,3).value = "*"
end with


end sub

Is there a way to change this input:

twenty four Number of hours for earth to rotate around its axis.
laser Monochromatic light
proton Positively charged particle of an atom.
acceleration Force equals mass times ____________.




To this format which can then be picked up by a program?

twenty four
laser
proton
acceleration
*
Number of hours for earth to rotate around its axis.
Monochromatic light
Positively charged particle of an atom.
Force equals mass times ____________.

[snip]

Oops, forgot to mention that row A is a header row. Sorry. <g>

 

[StargateFan]

sub test()

dim i as integer


with range("A2")
for i = 0 to 3
.offset(i,3).value = .offset(i,0).value
.offset(i+5,3).value = .offset(i,1).value
next i
.offset(4,3).value = "*"
end with


end sub

Thank you! Will give this a try later today. D

Is there a way to change this input:

twenty four Number of hours for earth to rotate around its axis.
laser Monochromatic light
proton Positively charged particle of an atom.
acceleration Force equals mass times ____________.




To this format which can then be picked up by a program?

twenty four
laser
proton
acceleration
*
Number of hours for earth to rotate around its axis.
Monochromatic light
Positively charged particle of an atom.
Force equals mass times ____________.

[snip]

Oops, forgot to mention that row A is a header row. Sorry. <g>

 

[StargateFanNotAtHome]

Thanks, it works a treat. It sends the text to outside the print area
and in dark cells into column D, but I can certainly live with that,
esp. considering the alternative. But what a marvellous bit of code.
I played with it, as I always do, when I get new code, but I'm just
not very good at scripting (even after all these years!!! <g>).

I just finetuned the script since I thought it would be easier to just
delete a sheet with the modified text than go back and erase the
output, even though I could script that, too. Just seems easier to
create a copy of the sheet.

***************************************
Sub Create_WordList()
ActiveSheet.Protect ' this makes sure main sheet is
protected

Application.Goto Reference:="R2C1"

Dim i As Integer

'Sheets("WordSheets wordlist maker").copy After:=Sheets(1)
ActiveSheet.copy After:=Sheets(1)
ActiveSheet.Unprotect 'place at the beginning of the code
Application.Goto Reference:="R1C1"

With Range("A2")
For i = 0 To 3
.Offset(i, 3).Value = .Offset(i, 0).Value
.Offset(i + 5, 3).Value = .Offset(i, 1).Value
Next i
.Offset(4, 3).Value = "*"
End With

Application.Goto Reference:="R2C1"

ActiveSheet.Protect ' place at end of code
End Sub
***************************************
Thanks! D