Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > convert a Word macro to an Excel macro
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 68
Default convert a Word macro to an Excel macro
Shauna Kelly wrote me a wonderful script but unfortunaltey she wrote it
in Word and i need it in Excel. can anyone help me convert this script
from Word to Excel?

Below is the script:

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

Thanks in advance

God bles
jsd219

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default convert a Word macro to an Excel macro
Maybe...

Option Explicit
Sub AddTextToCellsExcel()

Dim myCell as ranges
dim myRng as range

set myrng = selection
'or give it the range you need
'set myrng = worksheets("sheet9999").range("a1:c12")

for each mycell in myrng.cells
mycell.value = mycell.value & "CORE Instruction"
next mycell

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

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

End Sub

===
Or maybe...

Option Explicit
Sub AddTextToCellsExcel()

Dim myCell as ranges
dim myRng as range
dim sStart as string
dim sEnd as string

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

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

set myrng = selection
'or give it the range you need
'set myrng = worksheets("sheet9999").range("a1:c12")

for each mycell in myrng.cells
if lcase(mycell.value) like lcase(sstart & "*") then
mycell.value mycell.value & sEnd
next mycell

End Sub

jsd219 wrote:

Shauna Kelly wrote me a wonderful script but unfortunaltey she wrote it
in Word and i need it in Excel. can anyone help me convert this script
from Word to Excel?

Below is the script:

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

Thanks in advance

God bles
jsd219

--

Dave Peterson
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 68
Default convert a Word macro to an Excel macro
Both of them are giving me Compile error: User-defined type not defined
and then it highlights Dim myCell As ranges

God bless
jsd219

Dave Peterson wrote:
Maybe...

Option Explicit
Sub AddTextToCellsExcel()

Dim myCell as ranges
dim myRng as range

set myrng = selection
'or give it the range you need
'set myrng = worksheets("sheet9999").range("a1:c12")

for each mycell in myrng.cells
mycell.value = mycell.value & "CORE Instruction"
next mycell

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

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

End Sub

===
Or maybe...

Option Explicit
Sub AddTextToCellsExcel()

Dim myCell as ranges
dim myRng as range
dim sStart as string
dim sEnd as string

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

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

set myrng = selection
'or give it the range you need
'set myrng = worksheets("sheet9999").range("a1:c12")

for each mycell in myrng.cells
if lcase(mycell.value) like lcase(sstart & "*") then
mycell.value mycell.value & sEnd
next mycell

End Sub

jsd219 wrote:

Shauna Kelly wrote me a wonderful script but unfortunaltey she wrote it
in Word and i need it in Excel. can anyone help me convert this script
from Word to Excel?

Below is the script:

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

Thanks in advance

God bles
jsd219

--

Dave Peterson

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default convert a Word macro to an Excel macro
Typo...

Dim myCell as range

In fact, a few typos!

Option Explicit
Sub AddTextToCellsExcel()

Dim myCell As Range
Dim myRng As Range

Set myRng = Selection
'or give it the range you need
'set myrng = worksheets("sheet9999").range("a1:c12")

For Each myCell In myRng.Cells
myCell.Value = myCell.Value & "CORE Instruction"
Next myCell

End Sub

'or....


Sub AddTextToCellsExcel2()

Dim myCell As Range
Dim myRng As Range
Dim sStart As String
Dim sEnd As String

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

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

Set myRng = Selection
'or give it the range you need
'set myrng = worksheets("sheet9999").range("a1:c12")

For Each myCell In myRng.Cells
If LCase(myCell.Value) Like LCase(sStart & "*") Then
myCell.Value = myCell.Value & sEnd
End If
Next myCell

End Sub



jsd219 wrote:

Both of them are giving me Compile error: User-defined type not defined
and then it highlights Dim myCell As ranges

God bless
jsd219

Dave Peterson wrote:
Maybe...

Option Explicit
Sub AddTextToCellsExcel()

Dim myCell as ranges
dim myRng as range

set myrng = selection
'or give it the range you need
'set myrng = worksheets("sheet9999").range("a1:c12")

for each mycell in myrng.cells
mycell.value = mycell.value & "CORE Instruction"
next mycell

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

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

End Sub

===
Or maybe...

Option Explicit
Sub AddTextToCellsExcel()

Dim myCell as ranges
dim myRng as range
dim sStart as string
dim sEnd as string

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

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

set myrng = selection
'or give it the range you need
'set myrng = worksheets("sheet9999").range("a1:c12")

for each mycell in myrng.cells
if lcase(mycell.value) like lcase(sstart & "*") then
mycell.value mycell.value & sEnd
next mycell

End Sub

jsd219 wrote:

Shauna Kelly wrote me a wonderful script but unfortunaltey she wrote it
in Word and i need it in Excel. can anyone help me convert this script
from Word to Excel?

Below is the script:

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

Thanks in advance

God bles
jsd219

--

Dave Peterson

--

Dave Peterson
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 68
Default convert a Word macro to an Excel macro
Unfortunately neither one of those worked. the original script works
perfectly in word but my work is in excel. i am having problems with
the code below:

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

God bless
jsd219

Dave Peterson wrote:
Typo...

Dim myCell as range

In fact, a few typos!

Option Explicit
Sub AddTextToCellsExcel()

Dim myCell As Range
Dim myRng As Range

Set myRng = Selection
'or give it the range you need
'set myrng = worksheets("sheet9999").range("a1:c12")

For Each myCell In myRng.Cells
myCell.Value = myCell.Value & "CORE Instruction"
Next myCell

End Sub

'or....


Sub AddTextToCellsExcel2()

Dim myCell As Range
Dim myRng As Range
Dim sStart As String
Dim sEnd As String

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

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

Set myRng = Selection
'or give it the range you need
'set myrng = worksheets("sheet9999").range("a1:c12")

For Each myCell In myRng.Cells
If LCase(myCell.Value) Like LCase(sStart & "*") Then
myCell.Value = myCell.Value & sEnd
End If
Next myCell

End Sub



jsd219 wrote:

Both of them are giving me Compile error: User-defined type not defined
and then it highlights Dim myCell As ranges

God bless
jsd219

Dave Peterson wrote:
Maybe...

Option Explicit
Sub AddTextToCellsExcel()

Dim myCell as ranges
dim myRng as range

set myrng = selection
'or give it the range you need
'set myrng = worksheets("sheet9999").range("a1:c12")

for each mycell in myrng.cells
mycell.value = mycell.value & "CORE Instruction"
next mycell

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

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

End Sub

===
Or maybe...

Option Explicit
Sub AddTextToCellsExcel()

Dim myCell as ranges
dim myRng as range
dim sStart as string
dim sEnd as string

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

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

set myrng = selection
'or give it the range you need
'set myrng = worksheets("sheet9999").range("a1:c12")

for each mycell in myrng.cells
if lcase(mycell.value) like lcase(sstart & "*") then
mycell.value mycell.value & sEnd
next mycell

End Sub

jsd219 wrote:

Shauna Kelly wrote me a wonderful script but unfortunaltey she wrote it
in Word and i need it in Excel. can anyone help me convert this script
from Word to Excel?

Below is the script:

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

Thanks in advance

God bles
jsd219

--

Dave Peterson

--

Dave Peterson

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default convert a Word macro to an Excel macro
Excel doesn't have an activedocument.

Are you trying to automate changes to a word document from excel or are you
trying to change values in an excel file?

If you're changing an excel file, make sure you select the range to adjust
before you run either of the macros.

If that doesn't work, you may want to describe what you want in plain old words.

jsd219 wrote:

Unfortunately neither one of those worked. the original script works
perfectly in word but my work is in excel. i am having problems with
the code below:

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

God bless
jsd219

Dave Peterson wrote:
Typo...

Dim myCell as range

In fact, a few typos!

Option Explicit
Sub AddTextToCellsExcel()

Dim myCell As Range
Dim myRng As Range

Set myRng = Selection
'or give it the range you need
'set myrng = worksheets("sheet9999").range("a1:c12")

For Each myCell In myRng.Cells
myCell.Value = myCell.Value & "CORE Instruction"
Next myCell

End Sub

'or....


Sub AddTextToCellsExcel2()

Dim myCell As Range
Dim myRng As Range
Dim sStart As String
Dim sEnd As String

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

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

Set myRng = Selection
'or give it the range you need
'set myrng = worksheets("sheet9999").range("a1:c12")

For Each myCell In myRng.Cells
If LCase(myCell.Value) Like LCase(sStart & "*") Then
myCell.Value = myCell.Value & sEnd
End If
Next myCell

End Sub



jsd219 wrote:

Both of them are giving me Compile error: User-defined type not defined
and then it highlights Dim myCell As ranges

God bless
jsd219

Dave Peterson wrote:
Maybe...

Option Explicit
Sub AddTextToCellsExcel()

Dim myCell as ranges
dim myRng as range

set myrng = selection
'or give it the range you need
'set myrng = worksheets("sheet9999").range("a1:c12")

for each mycell in myrng.cells
mycell.value = mycell.value & "CORE Instruction"
next mycell

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

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

End Sub

===
Or maybe...

Option Explicit
Sub AddTextToCellsExcel()

Dim myCell as ranges
dim myRng as range
dim sStart as string
dim sEnd as string

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

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

set myrng = selection
'or give it the range you need
'set myrng = worksheets("sheet9999").range("a1:c12")

for each mycell in myrng.cells
if lcase(mycell.value) like lcase(sstart & "*") then
mycell.value mycell.value & sEnd
next mycell

End Sub

jsd219 wrote:

Shauna Kelly wrote me a wonderful script but unfortunaltey she wrote it
in Word and i need it in Excel. can anyone help me convert this script
from Word to Excel?

Below is the script:

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

Thanks in advance

God bles
jsd219

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Macro call Word Macro with Parameters Bill Sturdevant[_2_] Excel Programming 9 May 24th 07 12:21 AM
Need syntax for RUNning a Word macro with an argument, called from an Excel macro Steve[_84_] Excel Programming 3 July 6th 06 07:42 PM
WORD-DELIMITED string vba macro for excel/word jackal2k6 Excel Programming 3 December 23rd 05 04:32 PM
passing arguments from an excel macro to a word macro KWE39 Excel Discussion (Misc queries) 1 July 7th 05 03:56 PM
Macro - Open Word with Excel macro Bill Excel Discussion (Misc queries) 3 May 23rd 05 11:21 PM


All times are GMT +1. The time now is 06:47 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement