Solved

Updating Currency

Posted on 2013-11-26
14
179 Views
Last Modified: 2013-11-27
EE Pros,

Macroshadow worked with me on a Currency Conversion Program that I need to redo.  Attached is a WB with 3 WSs.  I'm looking for a macro that will appear on the first WS and update the MSN Currency chart (WS - Currency) and then place the correct Currency that is selected into two cells located on the other two WSs.

That's it!

Thank you in advance.

b.
Macros-and-formulas-to-support-C.xlsm
0
Comment
Question by:Bright01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
14 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39679827
Insert a button and assign a macro to it and insert this in the macro to update the table

Sheets("Currency").QueryTables(1).Refresh

Correcting G4, G5 to
''Customer-Inputs'!C9
''Price Quote'!F20

and inserting these formulas in  I4, I5
=INDIRECT(G4)
=INDIRECT(G5)

will pick values from the other sheets

Using this formula in I4, I5
will give the values in the selected currency
=INDIRECT(G4)*INDEX(J4:R4,0,MATCH('Customer-Inputs'!$F$2,Currency!$J$3:$R$3))/H4
=INDIRECT(G5)*INDEX(J5:R5,0,MATCH('Customer-Inputs'!$F$2,Currency!$J$3:$R$3))/H5
0
 

Author Comment

by:Bright01
ID: 39680492
The Update Table works!  The provisioning of the value selected doesn't work yet.  Take a look.....

B.
Currency-Update-and-Display.xlsm
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39680516
Delete one of the apostrophes from each of G4 and G5. You will be left with two each. One hidden and one displayed.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:Bright01
ID: 39680598
Now I'm getting a -0- instead of the selected currency.  Take a look.
Currency-Update-and-Display.xlsm
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39680626
That formula was supposed to pick up values from the yellow cells on the other two sheets which are presently empty. If you want the values picked from the pound column then change those formulas to

=INDEX(J4:R4,0,MATCH('Customer-Inputs'!$F$2,Currency!$J$3:$R$3))
=INDEX(J5:R5,0,MATCH('Customer-Inputs'!$F$2,Currency!$J$3:$R$3))

And also make sure that the spelling in Customer!F2 "exactly" maches one of the column headers on Currency sheet.
0
 

Author Comment

by:Bright01
ID: 39680697
OK... I got it.  Now I see what is the problem.  The right formula for the Currency Value is:


=INDEX(J4:R4,0,MATCH('Customer-Inputs'!$F$2,Currency!$J$3:$R$3))

BUT;

What is suppose to happen is once the formula provides the value (above), then the macro should place this value into the Cell that is Cell Referenced (in column G).  That's the part that's missing.  I should have been more clear on what I was looking for.

Thank you,

B.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39680876
Ok try this

Sub CurrencyUpdate()
Sheets("Currency").QueryTables(1).Refresh
With Sheets("Currency").Range("G4")
    Sheets(Replace(Split(.Value, "!")(0), "'", "")).Range(Split(.Value, "!")(1)).Value = .Offset(, 2).Value
End With
With Sheets("Currency").Range("G5")
    Sheets(Replace(Split(.Value, "!")(0), "'", "")).Range(Split(.Value, "!")(1)).Value = .Offset(, 2).Value
End With
End Sub
0
 

Author Comment

by:Bright01
ID: 39681017
Perfect!  Works well.  Only one last question; as I scale this, I'm going to have to put in multiple lines of this...... ( I have 50 lines!).

With Sheets("Currency").Range("G4")
    Sheets(Replace(Split(.Value, "!")(0), "'", "")).Range(Split(.Value, "!")(1)).Value = .Offset(, 2).Value
End With
With Sheets("Currency").Range("G5")
    Sheets(Replace(Split(.Value, "!")(0), "'", "")).Range(Split(.Value, "!")(1)).Value = .Offset(, 2).Value

Is there a way to reference the range without replicating the lines over and over?

B.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39681154
Try this. I have not tested it.

For Each cel In Sheets("Currency").Range("G4:G5")
    Sheets(Replace(Split(cel.Value, "!")(0), "'", "")).Range(Split(cel.Value, "!")(1)).Value = cel.Offset(, 2).Value
Next cel
0
 

Author Comment

by:Bright01
ID: 39681280
I get an error that "cel" is not defined.

B.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39681512
Then define it as a range
0
 

Author Comment

by:Bright01
ID: 39681571
So simply define "cel" as a Range Name?  I'm confused....sorry.  Do I simply list the range as G:G so it will accommodate all of the additional lines?

B.
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 39681606
dim cel as range
For Each cel In Sheets("Currency").Range("G4:G10000")
if cel.value <> "" then
    Sheets(Replace(Split(cel.Value, "!")(0), "'", "")).Range(Split(cel.Value, "!")(1)).Value = cel.Offset(, 2).Value
endif
Next cel
0
 

Author Closing Comment

by:Bright01
ID: 39681977
That got it!  Thank you.

Have a happy thanksgiving......


B.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question