pay scale formula

I am trying to show a pay scale for weight and shipping of a product. In
column "A" the weight. "B" is my base shipping rate is $160.00. In column "C"
is weight $3.00 per 100 lbs. and "D" is the total cost of shipping.
A B C D
10,100 $160.00 $303.00 $463.00
10,200 $160.00 $306.00 $466.00
I need "C" to automaticly add $3.00 as the weight incerases and I need "D"
to reflect the total of "B" and "C" as you see here. I have to do this up to
80,000 Lbs and to do it individualy is killin me. Does anyone know how in
MSWord i can make it do this for me to save me countless hours of adding by 3?

Embed an Excel table.



"Mike" wrote in message
...
I am trying to show a pay scale for weight and shipping of a product. In
column "A" the weight. "B" is my base shipping rate is $160.00. In column
"C"
is weight $3.00 per 100 lbs. and "D" is the total cost of shipping.
A B C D
10,100 $160.00 $303.00 $463.00
10,200 $160.00 $306.00 $466.00
I need "C" to automaticly add $3.00 as the weight incerases and I need
"D"
to reflect the total of "B" and "C" as you see here. I have to do this up
to
80,000 Lbs and to do it individualy is killin me. Does anyone know how in
MSWord i can make it do this for me to save me countless hours of adding
by 3?

I think that is what I am wanting to do, however I dont know how to make
excel do this either. I have looked at the help section and dont quite
understand how to do this. Can you talk me through the commands to make this
work?

"Jezebel" wrote:

Embed an Excel table.



"Mike" wrote in message
...
I am trying to show a pay scale for weight and shipping of a product. In
column "A" the weight. "B" is my base shipping rate is $160.00. In column
"C"
is weight $3.00 per 100 lbs. and "D" is the total cost of shipping.
A B C D
10,100 $160.00 $303.00 $463.00
10,200 $160.00 $306.00 $466.00
I need "C" to automaticly add $3.00 as the weight incerases and I need
"D"
to reflect the total of "B" and "C" as you see here. I have to do this up
to
80,000 Lbs and to do it individualy is killin me. Does anyone know how in
MSWord i can make it do this for me to save me countless hours of adding
by 3?

In Excel, you would just type in two (or at most three) of the amounts at
the desired interval and then use AutoFill to drag the rest of the cells.
For the $160.00 column, type $160.00 twice and then drag to AutoFill. The
totals column of course will be a sum formula, which you can also drag to
AutoFill.

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

"Mike" wrote in message
...
I think that is what I am wanting to do, however I dont know how to make
excel do this either. I have looked at the help section and dont quite
understand how to do this. Can you talk me through the commands to make
this
work?

"Jezebel" wrote:

Embed an Excel table.



"Mike" wrote in message
...
I am trying to show a pay scale for weight and shipping of a product.
In
column "A" the weight. "B" is my base shipping rate is $160.00. In
column
"C"
is weight $3.00 per 100 lbs. and "D" is the total cost of shipping.
A B C D
10,100 $160.00 $303.00 $463.00
10,200 $160.00 $306.00 $466.00
I need "C" to automaticly add $3.00 as the weight incerases and I
need
"D"
to reflect the total of "B" and "C" as you see here. I have to do this
up
to
80,000 Lbs and to do it individualy is killin me. Does anyone know how
in
MSWord i can make it do this for me to save me countless hours of
adding
by 3?

Mike,

Embedding the spreadsheet as Jezebel advises is probably best.
However, here is a crude macro to calculate and build your table. It
will take a minute or so, but just build create an 801 row table. Fill
in the headings and run the code.

Sub FillinPayScale()
Dim oTbl As Table
Dim i As Long
Dim x As Double
Dim y As Double
Set oTbl = ActiveDocument.Tables(1)
If oTbl.Columns.Count 4 Then
MsgBox "This only works for a four column table"
Exit Sub
End If
For i = 2 To oTbl.Rows.Count
With oTbl
.Cell(i, 1).Range.Text = (100 * i) - 100
.Cell(i, 2).Range.Text = "$160.00"
.Cell(i, 3).Range.Text = Format((3 * i) - 3, "$#,###.00")
x = Left(.Cell(i, 2).Range, Len(.Cell(i, 2).Range) - 2)
y = Left(.Cell(i, 3).Range, Len(.Cell(i, 3).Range) - 2)
.Cell(i, 4).Range = Format(x + y, "$#,###.00")
End With
Next
End Sub

Greg,
I did a cut and paste on this code. as soon as I tell it to run it gives me
an error.

Compile Error:
User-Defined type not defined

the following is hilighted "Dim oTbl As Table"

Does anyone know what this means? and what I do about it?

"Greg" wrote:

Mike,

Embedding the spreadsheet as Jezebel advises is probably best.
However, here is a crude macro to calculate and build your table. It
will take a minute or so, but just build create an 801 row table. Fill
in the headings and run the code.

Sub FillinPayScale()
Dim oTbl As Table
Dim i As Long
Dim x As Double
Dim y As Double
Set oTbl = ActiveDocument.Tables(1)
If oTbl.Columns.Count 4 Then
MsgBox "This only works for a four column table"
Exit Sub
End If
For i = 2 To oTbl.Rows.Count
With oTbl
.Cell(i, 1).Range.Text = (100 * i) - 100
.Cell(i, 2).Range.Text = "$160.00"
.Cell(i, 3).Range.Text = Format((3 * i) - 3, "$#,###.00")
x = Left(.Cell(i, 2).Range, Len(.Cell(i, 2).Range) - 2)
y = Left(.Cell(i, 3).Range, Len(.Cell(i, 3).Range) - 2)
.Cell(i, 4).Range = Format(x + y, "$#,###.00")
End With
Next
End Sub

Mike,

I am just fishing here, because a precise answer is not in my head.
However, this sounds like you are missing the Microsoft Word (Your Version)
Object Library or it isn't registered. In the VB editor, click on
ToolsReferences and see if it is listed and checked.

If not try:
Installing your Office CD and then run the command D:\setup /y /r (where D
is
the driveletter for your CD drive) This should re-register everything in
Office.

If this doesn't work, then you may need to re-install Office.



BTW

Some friends in the VBA group provided a tech assist and speeded things up
significantly. Use:



Sub FillinRateScale()
Dim oTbl As Table
Dim i As Long
Dim x As Double
Dim y As Double
Dim x As Long
Dim pCell(1 To 4) As Cell
Set oTbl = ActiveDocument.Tables(1)
x = oTbl.Rows.Count
For i = 2 To x
With oTbl
Set pCell(1) = .Cell(i, 1)
Set pCell(2) = .Cell(i, 2)
Set pCell(3) = .Cell(i, 3)
Set pCell(4) = .Cell(i, 4)
pCell(1).Range.Text = (100 * i) - 100
pCell(2).Range.Text = "$160.00"
pCell(3).Range.Text = Format((3 * i) - 3, "$#,###.00")
x = Left(pCell(2).Range, Len(pCell(2).Range) - 2)
y = Left(pCell(3).Range, Len(pCell(3).Range) - 2)
pCell(4).Range.Text = Format(x + y, "$#,###.00")
End With
Next
End Sub












--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Mike wrote:
Greg,
I did a cut and paste on this code. as soon as I tell it to run it
gives me an error.

Compile Error:
User-Defined type not defined

the following is hilighted "Dim oTbl As Table"

Does anyone know what this means? and what I do about it?

"Greg" wrote:

Mike,

Embedding the spreadsheet as Jezebel advises is probably best.
However, here is a crude macro to calculate and build your table. It
will take a minute or so, but just build create an 801 row table.
Fill in the headings and run the code.

Sub FillinPayScale()
Dim oTbl As Table
Dim i As Long
Dim x As Double
Dim y As Double
Set oTbl = ActiveDocument.Tables(1)
If oTbl.Columns.Count 4 Then
MsgBox "This only works for a four column table"
Exit Sub
End If
For i = 2 To oTbl.Rows.Count
With oTbl
.Cell(i, 1).Range.Text = (100 * i) - 100
.Cell(i, 2).Range.Text = "$160.00"
.Cell(i, 3).Range.Text = Format((3 * i) - 3, "$#,###.00")
x = Left(.Cell(i, 2).Range, Len(.Cell(i, 2).Range) - 2)
y = Left(.Cell(i, 3).Range, Len(.Cell(i, 3).Range) - 2)
.Cell(i, 4).Range = Format(x + y, "$#,###.00")
End With
Next
End Sub

Object declarations should always include the source library --

Dim oTbl As Word.Table

Apart from avoiding ambiguities, you'll get better diagnostics when things
go wrong.





"Greg Maxey" wrote in message
...
Mike,

I am just fishing here, because a precise answer is not in my head.
However, this sounds like you are missing the Microsoft Word (Your
Version) Object Library or it isn't registered. In the VB editor, click
on ToolsReferences and see if it is listed and checked.

If not try:
Installing your Office CD and then run the command D:\setup /y /r (where D
is
the driveletter for your CD drive) This should re-register everything in
Office.

If this doesn't work, then you may need to re-install Office.



BTW

Some friends in the VBA group provided a tech assist and speeded things up
significantly. Use:



Sub FillinRateScale()
Dim oTbl As Table
Dim i As Long
Dim x As Double
Dim y As Double
Dim x As Long
Dim pCell(1 To 4) As Cell
Set oTbl = ActiveDocument.Tables(1)
x = oTbl.Rows.Count
For i = 2 To x
With oTbl
Set pCell(1) = .Cell(i, 1)
Set pCell(2) = .Cell(i, 2)
Set pCell(3) = .Cell(i, 3)
Set pCell(4) = .Cell(i, 4)
pCell(1).Range.Text = (100 * i) - 100
pCell(2).Range.Text = "$160.00"
pCell(3).Range.Text = Format((3 * i) - 3, "$#,###.00")
x = Left(pCell(2).Range, Len(pCell(2).Range) - 2)
y = Left(pCell(3).Range, Len(pCell(3).Range) - 2)
pCell(4).Range.Text = Format(x + y, "$#,###.00")
End With
Next
End Sub












--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Mike wrote:
Greg,
I did a cut and paste on this code. as soon as I tell it to run it
gives me an error.

Compile Error:
User-Defined type not defined

the following is hilighted "Dim oTbl As Table"

Does anyone know what this means? and what I do about it?

"Greg" wrote:

Mike,

Embedding the spreadsheet as Jezebel advises is probably best.
However, here is a crude macro to calculate and build your table. It
will take a minute or so, but just build create an 801 row table.
Fill in the headings and run the code.

Sub FillinPayScale()
Dim oTbl As Table
Dim i As Long
Dim x As Double
Dim y As Double
Set oTbl = ActiveDocument.Tables(1)
If oTbl.Columns.Count 4 Then
MsgBox "This only works for a four column table"
Exit Sub
End If
For i = 2 To oTbl.Rows.Count
With oTbl
.Cell(i, 1).Range.Text = (100 * i) - 100
.Cell(i, 2).Range.Text = "$160.00"
.Cell(i, 3).Range.Text = Format((3 * i) - 3, "$#,###.00")
x = Left(.Cell(i, 2).Range, Len(.Cell(i, 2).Range) - 2)
y = Left(.Cell(i, 3).Range, Len(.Cell(i, 3).Range) - 2)
.Cell(i, 4).Range = Format(x + y, "$#,###.00")
End With
Next
End Sub