Macro to Sum Two Adjacent Columns as per Text in previous Column -Help required

[Reha]

Hello All,

I got this macro from this newsgroup (courtesy Don Guillet).
It looks up the word “Grade” and Sum two adjacent columns. The macro
runs fine if it is run from an Excel Workbook.


Sub SumColumns1SAS()
'SalesAid Software (e-mail address removed)
Dim mc As Range
Set mc = Cells.Find(What:="Grade", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase _
:=False, SearchFormat:=False)
If Not mc Is Nothing Then
End If
mc.Offset(, 1).Resize(, 2).FormulaR1C1 = _
"=SUM(R[-" & mc.Row - 1 & "]C:R[-1]C)"
End Sub

I am importing some Word Tables to Excel using the following code
stored in Normal.dot. on the Server (running Server 2003/Office
2003). This code is saved as Module 1

Dim i As Long, j As Long, xi As Long, k As Long
Dim wdrange As Range
Dim xlapp As Object
Dim xlbook As Object
Dim xlsheet As Object
Dim xlrange As Object
'added for file save
Dim StrFile As String
Dim StrPath As String
Dim StrName As String
'Defines ActiveDocument Name for File Saving
StrFile= ActiveDocument.Name
StrPath = ActiveDocument.Path
StrName= Left(StrFile, Len(StrFile) - 4)
On Error Resume Next
Set xlapp = GetObject(, "Excel.Application")
If Err Then
bstartApp = True
Set xlapp = CreateObject("Excel.Application")
End If
On Error GoTo 0
Set xlbook = xlapp.Workbooks.Add
Set xlsheet = xlbook.Worksheets(1)

.......the rest of the code to import Word Table follows..

After the data is transferred to Excel I call this routine saved as
Module 2 in Normal.dot to sum specific columns in the newly created
Excel Sheet.

Call SumColumns1SAS

xlbook.SaveAs sFileName

The code as mentioned above works fine from Excel Application. If I
call it while importing Word Tables to Excel the code gives Compile
error “Method or Data Member” not found and highlights mc.Row in the
following line:

"=SUM(R[-" & mc.Row - 1 & "]C:R[-1]C)"

Can this be rectified to work when called from Module 1? The modified
code should also work if there are more than one instance of Word and
Excel running as we work in a network environment and many users
generate various other reports using Word Application with the same
code.

Thanks for any assistance that can be provided please.

Reha