Add text to begining of cells within a range based on specified criteria

J

jsd219

This is a tuff one (at least for me). I need to select the contents of
a cell based on the contents of another cell and then I need to add
that text to the begining of other cells until I reach the destination
cell.

below is an example of what i am trying to do:

STANDARDS FOR FOREIGN LANGUAGE LEARNING
Vocabulario en acción 1
Communication 1.1 Students engage in conversations, provide and obtain
information, express feelings and emotions, and exchange opinions
Communication 1.2 Students understand and interpret written and spoken
language on a variety of topics
Communication 1.3 Students present information, concepts, and ideas to
an audience of listeners or readers on a variety of topics
Cultures 2.1 Students demonstrate an understanding of the relationship
between the practices and perspectives of the culture studied
Comparisons 4.2 Students demonstrate understanding of the concept of
culture through comparisons of the cultures studied and their own
CORE INSTRUCTION

I need to find cells with "STANDARDS FOR FOREIGN LANGUAGE LEARNING" i
then need to extract the contents of the cell below it "Vocabulario en
acción 1" i need to delete this row and then paste this at the
begining of all the cells below until i reach "CORE INSTRUCTION"
below is the end result:

STANDARDS FOR FOREIGN LANGUAGE LEARNING
Vocabulario en acción 1 - Communication 1.1 Students engage in
conversations, provide and obtain information, express feelings and
emotions, and exchange opinions
Vocabulario en acción 1 - Communication 1.2 Students understand and
interpret written and spoken language on a variety of topics
Vocabulario en acción 1 - Communication 1.3 Students present
information, concepts, and ideas to an audience of listeners or readers
on a variety of topics
Vocabulario en acción 1 - Cultures 2.1 Students demonstrate an
understanding of the relationship between the practices and
perspectives of the culture studied
Vocabulario en acción 1 - Comparisons 4.2 Students demonstrate
understanding of the concept of culture through comparisons of the
cultures studied and their own
CORE INSTRUCTION

I have 7000-8000 rows if info similar to this but it varies in the
middle. i would love to be able to have a box ask me for the text to
look for i.e. "STANDARDS FOR FOREIGN LANGUAGE LEARNING" and then a box
asking me for the text to end "CORE INSTRUCTION"

If anyone can help with this i would really appreciate it

God bless
jsd219
 
S

Shauna Kelly

Hi jsd219

I'm always a bit wary of deleting objects in a collection while cycling
through the collection, but bearing that in mind, try something like the
following:

Option Explicit

Sub AddTextToCells()

Dim sStart As String
Dim sCopy As String
Dim sEnd As String

Dim oTable As Word.Table
Dim oRow As Word.Row
Dim rngCell As Word.Range
Dim rngCopy As Word.Range

Dim bReplace As Boolean


sStart = InputBox(Prompt:="Text to search for", _
Default:="STANDARDS FOR FOREIGN LANGUAGE LEARNING")

sEnd = InputBox(Prompt:="Text to end with", _
Default:="CORE INSTRUCTION")


Set oTable = ActiveDocument.Tables(1)
For Each oRow In oTable.Rows
'Get the range of the cell
Set rngCell = oRow.Cells(1).Range

'Omit the end of cell marker
rngCell.End = rngCell.End - 1

'Process the cell
If rngCell.Text = sStart Then
bReplace = True

'Get the replacement text
Set rngCopy = oRow.Next.Cells(1).Range
rngCopy.End = rngCopy.End - 1
sCopy = rngCopy.Text

'Delete the row following our row
oRow.Next.Delete

ElseIf rngCell.Text = sEnd Then
bReplace = False
ElseIf bReplace Then
rngCell.InsertBefore sCopy & " - "
End If
Next oRow


End Sub

Hope this helps.

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word


This is a tuff one (at least for me). I need to select the contents of
a cell based on the contents of another cell and then I need to add
that text to the begining of other cells until I reach the destination
cell.

below is an example of what i am trying to do:

STANDARDS FOR FOREIGN LANGUAGE LEARNING
Vocabulario en acción 1
Communication 1.1 Students engage in conversations, provide and obtain
information, express feelings and emotions, and exchange opinions
Communication 1.2 Students understand and interpret written and spoken
language on a variety of topics
Communication 1.3 Students present information, concepts, and ideas to
an audience of listeners or readers on a variety of topics
Cultures 2.1 Students demonstrate an understanding of the relationship
between the practices and perspectives of the culture studied
Comparisons 4.2 Students demonstrate understanding of the concept of
culture through comparisons of the cultures studied and their own
CORE INSTRUCTION

I need to find cells with "STANDARDS FOR FOREIGN LANGUAGE LEARNING" i
then need to extract the contents of the cell below it "Vocabulario en
acción 1" i need to delete this row and then paste this at the
begining of all the cells below until i reach "CORE INSTRUCTION"
below is the end result:

STANDARDS FOR FOREIGN LANGUAGE LEARNING
Vocabulario en acción 1 - Communication 1.1 Students engage in
conversations, provide and obtain information, express feelings and
emotions, and exchange opinions
Vocabulario en acción 1 - Communication 1.2 Students understand and
interpret written and spoken language on a variety of topics
Vocabulario en acción 1 - Communication 1.3 Students present
information, concepts, and ideas to an audience of listeners or readers
on a variety of topics
Vocabulario en acción 1 - Cultures 2.1 Students demonstrate an
understanding of the relationship between the practices and
perspectives of the culture studied
Vocabulario en acción 1 - Comparisons 4.2 Students demonstrate
understanding of the concept of culture through comparisons of the
cultures studied and their own
CORE INSTRUCTION

I have 7000-8000 rows if info similar to this but it varies in the
middle. i would love to be able to have a box ask me for the text to
look for i.e. "STANDARDS FOR FOREIGN LANGUAGE LEARNING" and then a box
asking me for the text to end "CORE INSTRUCTION"

If anyone can help with this i would really appreciate it

God bless
jsd219
 
J

jsd219

Thank you so much, only one problem though: I am getting a Compile
error: User-defined type not defined and it highlights oTable As
Word.Table

God bless
jason
 
J

jsd219

Ok i figured out that you wrote this in word so i added the REF to Word
under tools. it starts to run but now it doen't like the line: Set
oTable = ActiveDocument.Tables(1)

God bless
jason
 
S

Shauna Kelly

Hi jsd219

If this code isn't running in Word (and you don't say where it is running)
then the code won't know what ActiveDocument is. You'll need to create a
variable that refers to the instance of Word you're using and get a
reference to the document against which you want the code to run.

Hope this helps.

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word
 
J

jsd219

I am using excel: I tried several combinations like
ActiveWorkbook.ActiveSheet and other variations but to no avail.

God bless
jsd219
 
S

Shauna Kelly

Hi jsd219

You posted your question to a newsgroup that deals with Word.

Are you manipulating an Excel workbook with VBA code running in Excel, or
are you manipulating a Word document from code running in Excel?

If you're manipulating an Excel workbook, and not a Word document, then the
basic logic of the code I supplied would apply, but you would need to change
all the objects to the relevant Excel objects.

Shauna

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word
 
J

jsd219

Yes i am working with an Excel workbook with VBA code running in Excel.
Sorry about the confusion, in desperation i posted to four different
newsgroups one of which was the word newsgroup. i though they should be
pretty close and i could make the possible adjustments. Dave Peterson
converted it for me. thank you so much for the help. :)

God bless
jsd219
 
S

Shauna Kelly

Hi jsd219

The people who answer questions on newsgroups are volunteers. To avoid
wasting their time, may I suggest that next time you have a question that
you write one message to one relevant newsgroup, and explicitly say what
product you are using.

Thanks.

Shauna

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add text to begining of cells within a range based on specified criteria 5
Add text to begining of cells within a range based on specified criteria 2
select text in cell based on text from another cell, paste the text at the begining of a thrid cell, 0
Summing a range of cells based on criteria in another range 2
Add values based on text color with the criteria of a date 7
Combobox to determine text for a range of cells 0
Query to select a range of time, based on a single time 2
convert a Word macro to an Excel macro 36

Top