Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-11-2017, 02:08 AM
Stockturn Stockturn is offline Manipulating Data in Cells Windows 10 Manipulating Data in Cells Office 2010 32bit
Novice
Manipulating Data in Cells
 
Join Date: Sep 2017
Location: Somerset UK
Posts: 1
Stockturn is on a distinguished road
Default Manipulating Data in Cells

Hi All,

It's more than likely that I'm trying to achieve the impossible; but here is my problem.



I have a supplier who sends me a Word Document which is a Price List with the products priced in their local currency and I need to convert this into my local currency; sounds simple on the face of it but I have several problems

Problems: -
  1. I need to retain the document format; it contains Headers/Footers, Banners and Graphics and I must not lose these.
  2. The Exchange Rate is constantly moving, albeit I do have a fixed 90 Day Rate.
  3. The document contains multiple Tables (1 for each Product).

Currently I am sitting down with a calculator and manually editing each cell, a process which takes several hours to complete. I have been scratching my head for a few days now find some easy solution to this and automate the manipulation of the cell holding the prices. I'll include a screen snip so you can see what I'm up against.

Any feedback would be cool.
Attached Images
File Type: jpg PList1.JPG (69.1 KB, 13 views)
Reply With Quote
  #2  
Old 09-11-2017, 04:45 AM
macropod's Avatar
macropod macropod is offline Manipulating Data in Cells Windows 7 64bit Manipulating Data in Cells Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

You could do the conversions with a macro like:
Code:
Sub ConvCurrency()
Application.ScreenUpdating = False
Dim Tbl As Table, r As Long, c As Long, xchg As Single, strVal As String
xchg = CSng(InputBox("Exchange Rate to Apply?", "ForEx Conversion", "1.00"))
If xchg <= 0 Then Exit Sub
For Each Tbl In ActiveDocument.Tables
  With Tbl
    c = .Columns.Count
    For r = 1 To .Rows.Count
      With .Cell(r, c).Range
        strVal = Split(.Text, vbCr)(0)
        If IsNumeric(strVal) Then
          .Text = Format(CSng(strVal) * xchg, ",0.00")
        Else
          .Text = "USD"
        End If
      End With
    Next
  End With
Next
Application.ScreenUpdating = True
End Sub
For PC macro installation & usage instructions, see: http://www.gmayor.com/installing_macro.htm
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Manipulating 3D objects in Powerpoint 2013 MScrazy PowerPoint 0 05-27-2014 02:29 PM
How to populate cells in Sheet2 with Data Source query using cell data from Sheet1 bobznkazoo Excel 2 03-27-2014 11:14 AM
Manipulating Task data from email. dvad Outlook 0 05-10-2011 01:55 PM
manipulating cross-reference fields _wim_ Word 0 12-10-2010 05:52 AM
Manipulating checkboxes in XML Ivo Word 0 12-06-2005 09:04 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:29 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft