Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table in Word that acts as a price list. How can I use a formula to increase each individual cells cost by for example 5% and then round up the figure to the nearest pound. I would appreciate anyones help in this matter. With Thanks Darron Cook

400 500 600 700 80
400 90 94 99 103 11
500 93 98 102 106 11
600 97 101 105 110 12
700 101 106 110 114 12
800 105 109 113 117 13
900 109 113 117 122 13
1000 112 116 121 125 13
1100 118 122 126 131 14
1200 121 125 130 134 14
1300 125 129 133 137 15
 
Assuming that it is the first table in the document and that the first row
and the first column are headings, use a macro containing the following code

Dim myrange As Range, i As Long, j As Long, increase As String
increase = InputBox("Enter the percentage by which you want to increase the
prices.", "Revise Prices", 5)
If IsNumeric(increase) Then
For i = 2 To ActiveDocument.Tables(1).Rows.Count
For j = 2 To ActiveDocument.Tables(1).Columns.Count
Set myrange = ActiveDocument.Tables(1).Cell(i, j).Range
myrange.End = myrange.End - 1
myrange = myrange * (1 + increase / 100)
If myrange - Int(myrange) > 0 Then
myrange = Int(myrange) + 1
End If
ActiveDocument.Tables(1).Cell(i, j).Range = myrange
Next j
Next i
ElseIf increase = "" Then
Exit Sub
Else
MsgBox "You must enter a number"
End If


--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP
 
Back
Top