Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.tables
|
|||
|
|||
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? |
#2
Posted to microsoft.public.word.tables
|
|||
|
|||
pay scale formula
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? |
#3
Posted to microsoft.public.word.tables
|
|||
|
|||
pay scale formula
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? |
#4
Posted to microsoft.public.word.tables
|
|||
|
|||
pay scale formula
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? |
#5
Posted to microsoft.public.word.tables
|
|||
|
|||
pay scale formula
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 |
#6
Posted to microsoft.public.word.tables
|
|||
|
|||
pay scale formula
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 |
#7
Posted to microsoft.public.word.tables
|
|||
|
|||
pay scale formula
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 |
#8
Posted to microsoft.public.word.tables
|
|||
|
|||
pay scale formula
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 |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Help | Microsoft Word Help | |||
Formula to Sum values in word table | Mailmerge | |||
scale of items inserted from scanner | Microsoft Word Help | |||
I need a formula of amount of hours from two times in two cell? | Tables | |||
Formula in table - Creating a final total from sub-total cells | Tables |