pay scale formula

[Mike]

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?

 

[Jezebel]

Embed an Excel table.

 

[Mike]

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?

 

[Suzanne S. Barnhill]

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.

 

[Greg]

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]

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 Maxey]

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
Tools>References 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

 

[Jezebel]

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.