Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 183
  • Last Modified:

Updating Currency

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
Bright01
Asked:
Bright01
  • 7
  • 7
1 Solution
 
Saqib Husain, SyedEngineerCommented:
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
 
Bright01Author Commented:
The Update Table works!  The provisioning of the value selected doesn't work yet.  Take a look.....

B.
Currency-Update-and-Display.xlsm
0
 
Saqib Husain, SyedEngineerCommented:
Delete one of the apostrophes from each of G4 and G5. You will be left with two each. One hidden and one displayed.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Bright01Author Commented:
Now I'm getting a -0- instead of the selected currency.  Take a look.
Currency-Update-and-Display.xlsm
0
 
Saqib Husain, SyedEngineerCommented:
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
 
Bright01Author Commented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
Bright01Author Commented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
Bright01Author Commented:
I get an error that "cel" is not defined.

B.
0
 
Saqib Husain, SyedEngineerCommented:
Then define it as a range
0
 
Bright01Author Commented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
Bright01Author Commented:
That got it!  Thank you.

Have a happy thanksgiving......


B.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now