Solved

Changing to Currency Format

Posted on 2013-11-28
5
237 Views
Last Modified: 2013-11-28
EE Pros,

Ssaqibh wrote a Currency Update Macro for me.  I have one change to make.  The Attached Workbook works fine except the Currency formatting doesn't change when making the Currency Selection.  The formatting needs to be in Currency - Column I Cell 4 and below.

That's it!

Thank you in advance.

B.
Currency-Update-and-Displayv2.xlsm
0
Comment
Question by:Bright01
  • 2
  • 2
5 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 39683472
Where do you set/select the currency?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39683490
Sub CurrencyUpdate()
Dim cel As Range, cn As Range
Sheets("Currency").QueryTables(1).Refresh
Set cn = Sheets("Currency").Range("J3:R3").Find(Sheets("Customer-inputs").Range("F2").Value, , xlValues)
For Each cel In Sheets("Currency").Range("G4:G10000")
cel.Offset(, 2).Value = Sheets("Currency").Cells(cel.Row, cn.Column).Value
cel.Offset(, 2).NumberFormat = Cells(cel.Row, cn.Column).NumberFormat
If cel.Value <> "" Then
    Sheets(Replace(Split(cel.Value, "!")(0), "'", "")).Range(Split(cel.Value, "!")(1)).Value = cel.Offset(, 2).Value
    Sheets(Replace(Split(cel.Value, "!")(0), "'", "")).Range(Split(cel.Value, "!")(1)).NumberFormat = cel.Offset(, 2).NumberFormat
End If
Next cel
End Sub

Open in new window

0
 

Author Comment

by:Bright01
ID: 39683579
Ssaq,

I changed the macro; however, now it appears it is doing a lot of additional work (see the flickering) when firing "Update".  

Can you take a quick look?

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

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 39683727
Move line 8 up to line 6
0
 

Author Closing Comment

by:Bright01
ID: 39683999
That works!  Will be authoring another question for another slight change to the program.

Thanks for hanging in there with me!

B.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

708 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now