asked on

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

Saqib Husain

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

Bright01

ASKER

The Update Table works! The provisioning of the value selected doesn't work yet. Take a look.....

B.
Currency-Update-and-Display.xlsm

Saqib Husain

Delete one of the apostrophes from each of G4 and G5. You will be left with two each. One hidden and one displayed.

Bright01

ASKER

Now I'm getting a -0- instead of the selected currency. Take a look.
Currency-Update-and-Display.xlsm

Saqib Husain

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.

Bright01

ASKER

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.

Saqib Husain

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

Bright01

ASKER

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.

Saqib Husain

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

Bright01

ASKER

I get an error that "cel" is not defined.

B.

Saqib Husain

Then define it as a range

Bright01

ASKER

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.

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bright01

ASKER

That got it! Thank you.

Have a happy thanksgiving......

B.